In this short code snippet article I will explain how to use and pass parameters in dynamic SQL in SQL Server using the sp_executesql function.
 
Database
 
I am making use of Microsoft Northwind database, you can find the instructions for downloading and installing in the article Install Microsoft Northwind and Pubs Sample databases in SQL Server Management Studio
 
Passing parameter to dynamic SQL in SQL Server
 
Generally we do as following i.e. we simply concatenate the parameter values to the SQL string.
 
CREATE PROCEDURE Customers_GetCustomer
      @CustId CHAR(5)
AS
BEGIN
      DECLARE @SQL NVARCHAR(2000)
      SET @SQL = 'SELECT ContactName FROM Customers WHERE CustomerId = ''' + @CustId + ''''
      EXEC(@SQL)
END
 
Though this works it is difficult to handle due to single quotes and also it is vulnerable to attacks like SQL Injection and hence you must make use of sp_executesql function and pass the parameter value as follows.
 
CREATE PROCEDURE Customers_GetCustomer
      @CustId CHAR(5)
AS
BEGIN
      DECLARE @SQL NVARCHAR(2000)
      SET @SQL = 'SELECT ContactName FROM Customers WHERE CustomerId = @CustomerId'
      EXEC sp_executesql @SQL, N'@CustomerId CHAR(5)', @CustomerId = @CustId
END
 
Thus in the above stored procedure, parameter values are passed to the dynamic SQL in SQL Server.