How to execute a sql statement with a parameter in SQL Management Studio?
Today I got asked how to execute a parameterized SQL query in SQL Management Studio.
You can do it two ways:
Method 1 – Use SP_EXECUTESQL stored procedure to execute parameterized SQL
DECLARE @query NVARCHAR(MAX) = N'SELECT * FROM customer WHERE CustomerId = @id' DECLARE @params NVARCHAR(MAX) = N'@id int'; EXEC SP_EXECUTESQL @query, @params, @id = 3;
If you need to do multiple parameters, it is pretty much the same syntax.
DECLARE @query NVARCHAR(MAX) = N'SELECT * FROM customer WHERE CustomerId IN (@id1, @id2)' DECLARE @params NVARCHAR(MAX) = N'@id1 int, @id2 int'; EXEC SP_EXECUTESQL @query, @params, @id1 = 3, @id2 = 4;
Method 2 – DECLARE Variables to execute parameterized SQL
DECLARE @id INT = 3; SELECT * FROM customer WHERE CustomerId = @id;
If you need to do multiple parameters, just declare another variable.
DECLARE @id1 INT = 3; DECLARE @id2 INT = 4; SELECT * FROM customer WHERE CustomerId IN (@id1, @id2);
