In this article I will explain with an example, how to write and execute dynamic SQL Query in SQL Server.
Dynamic SQL Query can be executed using EXEC and sp_executesql commands in SQL Server.
Execution of dynamic SQL is supported in SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012, 2014 or higher.
 
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.