What do you mean by SQL dynamic queries? How to execute SQl Dynamic Commands in SQl Server?

SQL Dynamic Query:

Sometime hard coded queries are not suitable to achieve the result. In that case we create dynamic query. SQL provide us different ways to create dynamic queries:

1.     Query With parameter

2.     Use Exec

3.     Use sp_executesql

1.     Query With Parameter:

This first approach is to pass parameters into WHERE clause of your SQL statement.  Suppose we need to find all records from the “EmDetails” table where City = 'Chandigarh'.  This can be done as:

 

  DECLARE @city varchar(75)

  SET @city = 'Chandigarh'

  SELECT * FROM EmDetails WHERE City = @city

2.     Use Exec:

Second option is to use EXEC, with this approach we built the SQL statement on the runtime (dynamically). 

Suppose we want to get columns EmpID, Name and City where City = 'Chandigarh'.

DECLARE @sqlCommand varchar(1000)

DECLARE @columnList varchar(75)

DECLARE @city varchar(75)

SET @columnList = 'EmpID, Name, City'

SET @city = '''Chandigarh'''

SET @sqlCommand = 'SELECT ' + @columnList + ' FROM EmDetails WHERE City = ' + @city

EXEC (@sqlCommand)

As we can see from above example handling the @city value is not at straight forward, because you also need to define the extra quotes in order to pass a character value into the query.  These extra quotes could also be done within the statement, but either way you need to specify the extra single quotes in order for the query to be built correctly and therefore run.

3.     Use Sp_ExecuteSql:

 In this approach we will use both first and second approach in one query. With this approach we have the ability to dynamically build the query and also able to still use parameters as you could in example 1. This saves the need to have to deal with the extra quotes to get the query to build correctly.  In addition, with using this approach you can ensure that the data values being passed into the query are the correct datatypes.

DECLARE @sqlCommand nvarchar(1000)

DECLARE @columnList varchar(75)

DECLARE @city varchar(75)

SET @columnList = 'EmpID, Name, City'

SET @city = 'Chandigarh'

SET @sqlCommand = 'SELECT ' + @columnList + ' FROM EmDetails WHERE City = @city'

EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city