How to build dynamic SQL statement in SQL Server

Er Tasneem Ahmad Karimi Saturday, November 07, 2020

SQL Server offers a few ways of running a dynamically built SQL statement. Here are a few options:

  1. Writing a query with parameters
  2. Using EXEC
  3. Using sp_executesql

 

  • Dynamic SQL by writing a query with parameters


This first approach is pretty straight forward if you only need to pass parameters into your WHERE clause of your SQL statement. Let's say we need to find all records from the customers table where  EmpName= 'Tasneem'. This can be done easily such as the following example shows.

DECLARE @EmpName varchar(75)

SET @EmpName= 'Tasneem'

SELECT * FROM EmployeeMaster WHERE EmpName = @EmpName

We can turn the above into a stored procedure as follows:

CREATE PROCEDURE getUserName @EmpName Varchar(20)

AS

Begin 

SELECT * FROM EmployeeMaster WHERE EmpName = @EmpName

END

  • Dynamic SQL commands using EXEC
Declare @sqlComand varchar(200)
Declare @columnList varchar(200)
Declare @city varchar(75)

Set @columnList='AddressID,AddressID2,City'
Set @city="'Delhi'"
Set @sqlCommand='Select '+@columnList+' FROM ADRESS Where City='+@city

EXEC (@sqlCommand)

  • Dynamic SQL commands using sp_executesql

Declare @sqlComand varchar(200)
Declare @columnList varchar(200)
Declare @city varchar(75)

Set @columnList='AddressID,AddressID2,City'
Set @city="'Delhi'"
Set @sqlCommand='Select '+@columnList+' FROM ADRESS Where City='+@city

Execute sp_executesql @sqlCommand, N'@city nvarchar(75)',@city=@city
Tags: