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);

Leave a Reply

Your email address will not be published. Required fields are marked *