Writing dynamic sql queries in sql server

There are many scenarios in which we need to change the columns name, tables name etc dynamically in SQL queries. Dynamic SQL is just like a normal SQL query except query is stored liked a string.

Very simple example of dynamic SQL: 

DECLARE @vcSqlQuery AS VARCHAR(MAX)

SET @vcSqlQuery = 'SELECT 1'

EXECUTE(@vcSqlQuery)

Why we need dynamic SQL in SQL server:

Consider a scenario we need to get the data from three tables, let say tblEmp, tblStu and tblOrder, while table name is passed in a parameter of a stored procedure:

We can do it without using dynamic SQL:

CREATE PROCEDURE uspGetData(

     @vcTableName AS VARCHAR(200)

)

AS

BEGIN

     IF @vcTableName = 'tblEmp' BEGIN END

          SELECT * FROM tblEmp

     END ELSE IF @vcTableName = 'tblStu' BEGIN END

          SELECT * FROM tblStu

     END IF @vcTableName = 'tblOrder' BEGIN END

          SELECT * FROM tblOrder

     END

END

It is good if we know the all possible tables name or all possible tables are very less. Otherwise, we will have to use dynamic SQL. Now we are writing the same procedure using dynamic SQL:

CREATE PROCEDURE uspGetData(

     @vcTableName AS VARCHAR(200)

)

AS

BEGIN

     DECLARE @vcSqlQuery AS VARCHAR(MAX)

     SET @vcSqlQuery = 'SELECT * FROM '

+ @vcTableName

     EXECUTE(@vcSqlQuery)

    

END

Query parametrization in case dynamic SQL:

In SQL server when any query is executed, at first time it is compiled and an execution plan is created. If a query has parametrized then it will change the parameters of query it will reuse the execution plan and saves the execution time instead of recompiling and creating a new execution plan. For example:

We are creating a table named tblEmployee:

CREATE TABLE tblEmployee(

     ntEmpID  BIGINT PRIMARY KEY,

     vcName VARCHAR(200),

     moSalary MONEY

)

Now we would like to execute this query using dynamic SQL:

DECLARE @vcName VARCHAR(200) = 'Scott'

DECLARE @moSalary MONEY = 500

SELECT *

FROM tblEmployee

     WHERE vcName = @vcName

     AND moSalary > @moSalary

Using Dynamic SQL:

DECLARE @vcName VARCHAR(200) = 'Scott'

DECLARE @moSalary MONEY = 500

DECLARE @vcSqlQuery AS VARCHAR(MAX)

SET @vcSqlQuery =

     'SELECT *

     FROM tblEmployee

          WHERE vcName = ''' + @vcName + '''' +

          ' AND moSalary > ' + CAST(@moSalary AS VARCHAR)

         

EXECUTE(@vcSqlQuery)

Before executing it first clean the cached plan:

--Do not execute it on production server

DBCC FREEPROCCACHE

Now again execute the above dynamic SQL query with some different parameters. For example:

DECLARE @vcName VARCHAR(200) = 'Greg'

Now we are going to check cached plan:

SELECT objtype,[text]

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'%tblEmployee%'

     AND text NOT LIKE '%sys.dm_exec_cached_plans%'

Output:

objtype	text
Adhoc	SELECT *   FROM tblEmployee    WHERE vcName = 'Greg' AND moSalary > 500.00
Adhoc	SELECT *   FROM tblEmployee    WHERE vcName = 'Scott' AND moSalary > 500.00
Prepared	(@1 varchar(8000),@2 numeric(5,2))SELECT * FROM [tblEmployee] WHERE [vcName]=@1 AND [moSalary]>@2

So in the cached plan, there are two entries for two different parameters. This type of queries are called ad-hoc queries and for each execution different parameter SQL server has to compile and create new execution plan which will decrease the performance of the application. 

We can write the dynamic SQL is parametrized ways:

DECLARE @vcName VARCHAR(200) = 'Scott'

DECLARE @moSalary MONEY = 500

DECLARE @vcSqlQuery NVARCHAR(MAX)

DECLARE @vcParameter AS NVARCHAR(100)

--Creating the SQL query

SET @vcSqlQuery = N'

     SELECT *

     FROM tblEmployee

          WHERE vcName = @vcName

          AND moSalary > @moSalary'

--Declaring the parameters       

SET @vcParameter = N'@vcName VARCHAR(200), @moSalary MONEY'

--Executing the dynamic query

EXECUTE SP_EXECUTESQL

     @vcSqlQuery,

     @vcParameter,

     @vcName,

     @moSalary

Here SP_EXECUTESQL is a system stored procedure.

Before executing the parameterized dynamic SQL first we are cleaning the cached plan:

--Do not execute it on production server

DBCC FREEPROCCACHE

Now again execute the above dynamic SQL query with some different parameters. For example:

DECLARE @vcName VARCHAR(200) = 'Greg'

Now we are going to check cached plan:

SELECT objtype,[text]

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'%tblEmployee%'

     AND text NOT LIKE '%sys.dm_exec_cached_plans%'

Output:

objtype	text
Prepared	(@vcName VARCHAR(200), @moSalary MONEY)   SELECT *    FROM tblEmployee     WHERE vcName = @vcName    AND moSalary > @moSalary

So now there is only one entry in the cached plan its mean SQL server is reusing the compiled execution plan.

Note: In the parameter, we can only pass the value of parameters of the query not the schema definition that is we cannot pass the table name, column name etc. as a parameter in the system procedure SP_EXECUTESQL.

An understanding single quote in dynamic SQL:

As we know in SQL server if we want to add a single quote on the string we have to write two single quotes. For example:

SELECT 'Exact '' Help' 

Output: Exact ' Help

If any single quote is inside two other single quotes (i.e. two-level nesting) then we will have to write four single and so on. For example:

--Two level nesting (2 * 2 = 4 single quote)

DECLARE @vcSqlQuery AS NVARCHAR(MAX)

SET  @vcSqlQuery = 'SELECT ''Exact '''' Help'''

EXECUTE SP_EXECUTESQL @vcSqlQuery

Output: Exact ' Help

--Three level nesting ( 2 * 2 * 2 = 8 single quotes)

DECLARE @vcSqlQuery AS NVARCHAR(MAX)

SET  @vcSqlQuery = N'EXECUTE(''SELECT ''''Exact '''''''' Help'''' '')' 

EXECUTE SP_EXECUTESQL  @vcSqlQuery

Output: Exact ' Help

We can also use double quote to create a string by setting QUOTED_IDENTIFIER off. For example:

DECLARE @vcSqlQuery AS NVARCHAR(MAX)

SET  @vcSqlQuery = 'SET QUOTED_IDENTIFIER OFF;

SELECT "Exact '' Help"'

EXECUTE SP_EXECUTESQL @vcSqlQuery

Output: Exact ' Help

The local variable in dynamic SQL:

Before we come to the main topic I would like to explain about the scope of variables in case of dynamic SQL. We are executing following dynamic SQL:

--Query 1

EXEC SP_EXECUTESQL N'DECLARE @ntID AS INT = 10'

SELECT @ntID

We will get an error message like: Must declare the scalar variable "@ntID".

The reason behind this is the scope of a variable which has been declared within dynamic SQL is different from the current scope. So the variable is not visible here also vice versa. For example:

--Query 2

DECLARE @ntID AS INT = 10

EXEC SP_EXECUTESQL N'SELECT @ntID'

Error message: Must declare the scalar variable "@ntID".

SO what is the solution for both the quires?  We will do it one by one.

A solution for query 1:

--Solution One

EXEC SP_EXECUTESQL N'DECLARE @ntID AS INT = 10; SELECT @ntID'

In solution one, we are not able to store the variable in a local variable for later use.

--Solution Two

DECLARE @ntLocalID AS INT

EXEC SP_EXECUTESQL

     N'DECLARE @ntID AS INT = 10; SET @ntLocalID = @ntID',

     N'@ntLocalID AS INT OUTPUT',

     @ntLocalID OUTPUT

    

SELECT @ntLocalID

A solution for query 2:

--Solution One

DECLARE @ntID AS INT = 10

DECLARE @vcSqlQuery AS NVARCHAR(MAX)

SET @vcSqlQuery = N'SELECT ' + CAST(@ntID AS VARCHAR) 

EXEC SP_EXECUTESQL @vcSqlQuery

In solution one, we are not able to store the variable in the local variable of dynamic SQL for later use.

--Solution Two

DECLARE @ntID AS INT = 10

EXEC SP_EXECUTESQL

     N'SELECT @ntID',

     N'@ntID AS INT',

     @ntID

A temporary table in dynamic SQL:

Just like local variable temporary tables of dynamic SQL has no scope in the current scope. For example:

--Query 1

EXEC SP_EXECUTESQL N'SELECT * INTO #Temp FROM sys.messages'

SELECT * FROM #Temp

Error message: Invalid object name '#Temp'.

While the opposite is not true. That is temporary tables in the current scope has scope within dynamic SQL. For example:

--Query 2

SELECT message_ID,severity INTO #Temp FROM sys.messages

EXEC SP_EXECUTESQL N'SELECT message_ID,severity FROM #Temp'

It will execute fine.

A solution for query 1:

--Solution one

--Using global or actual table

EXEC SP_EXECUTESQL N'SELECT message_ID,severity

INTO ##Temp

FROM sys.messages'

SELECT * FROM ##Temp

It is a bad solution to use global temporary table due to its scope. We can access the global temporary table in two different sessions. Even if we drop the table after using it we may get an error message like "There is already an object named '##Temp' in the database." if we will execute the same query in multiple times in different sessions.

--Solution two

CREATE TABLE #Temp(

     message_ID BIGINT,

     severity INT

)

INSERT INTO #Temp

EXEC SP_EXECUTESQL N'SELECT message_ID,severity

FROM sys.messages'

SELECT * FROM #Temp

Passing table variable as a parameter in dynamic SQL:

We can pass the current table variable which has no scope in the dynamic SQL as a parameter. For example:

--Create a table according to table schema

CREATE TYPE Temp AS TABLE(

     message_ID BIGINT,

     severity INT

)

--Declaring the table variable from table type

DECLARE @Temp AS Temp

--Inserting some records into the table variable @Temp

INSERT INTO @Temp

SELECT message_ID,severity FROM sys.messages

    

--Getting the data from table variable using dynamic SQL 

EXEC SP_EXECUTESQL

     N'SELECT message_ID,severity FROM @Temp ' ,

     N'@Temp  AS Temp READONLY ' ,

     @Temp

Error handling in dynamic SQL:

We can handle error occurred in a dynamic query just like a normal query that is using TRY CATCH block. For example:

CREATE PROCEDURE uspDySql

AS

BEGIN

     DECLARE @vcSqlQuery AS NVARCHAR(MAX)

    

     BEGIN TRY

         

          SET @vcSqlQuery = 

              N'SELECT ' +

                   'message_ID,' + 

                   'severity1 ' +

              'FROM sys.messages'

         

          EXEC SP_EXECUTESQL @vcSqlQuery

         

     END TRY

     BEGIN CATCH

          SELECT

              ERROR_MESSAGE() AS ErrMsg,

              ERROR_PROCEDURE() AS ErrProc,

              ERROR_LINE() AS ErrLine

         

     END CATCH

END

If we will execute this procedure:

EXECUTE dbo.uspDySql

We will get the output:

ErrMsg	ErrProc	ErrLine
Invalid column name 'severity1'.	NULL	1

Important points:

a. ERROR_PROCEDURE(): If an error occurs in dynamic SQL queries this function doesn't work and it will return NULL instead of procedure where an error occurred. It is due to a dynamic query is executed in a different scope and in that scope dynamic query is a normal query which is not inside any procedure. For example, we are creating a procedure which will throw some error:

CREATE PROC uspAdd

AS

     SELECT 1 + 'a'

Now we are executing this procedure using dynamic SQL:

BEGIN TRY

EXEC SP_EXECUTESQL N'EXECUTE uspAdd'

END TRY

BEGIN CATCH

     SELECT

          ERROR_MESSAGE() AS ErrMsg,

          ERROR_PROCEDURE() AS ErrProc

         

END CATCH

If we will execute it then the output will be:

ErrMsg	ErrProc
Conversion failed when converting the varchar value 'a' to data type int.	uspAdd

In this time ERROR_PROCEDURE() function is returning the name of procedure where this occurred. I hope you will get the concept.

b. ERROR_LINE(): In the dynamic SQL anywhere will error occur we will get error line equal to one. To resolve this issue we can add different lines in dynamic SQL. For example:

ALTER PROCEDURE uspDySql

AS

BEGIN

     DECLARE @vcSqlQuery AS NVARCHAR(MAX)

     DECLARE @vcLine AS CHAR(2) = CHAR(10) + CHAR(13)

    

     BEGIN TRY

         

          SET @vcSqlQuery = 

              N'SELECT ' + @vcLine +

                   'message_ID,' + @vcLine +

                   'severity1 ' + @vcLine +

              'FROM sys.messages'

         

          EXEC SP_EXECUTESQL @vcSqlQuery

         

     END TRY

     BEGIN CATCH

          SELECT

              ERROR_MESSAGE() AS ErrMsg,

              ERROR_PROCEDURE() AS ErrProc,

              ERROR_LINE() AS ErrLine

         

     END CATCH

END

SQL Cursor in dynamic SQL:

I would like to categorize this topic in three groups:

a. When cursor variable has both declared and used in dynamic SQL

b. When cursor variable has declared in the current scope and used in dynamic SQL

c. When cursor variable has declared in dynamic SQL and used in current scope

When cursor variable has both declared and used in dynamic SQL:

It is just like to normal cursor statement except it has written in dynamic SQL. For example:

DECLARE @vcSqlQuery AS NVARCHAR(MAX)

SET @vcSqlQuery =

'DECLARE @MsgId AS BIGINT

DECLARE curMsg CURSOR STATIC FORWARD_ONLY

FOR

SELECT TOP(5) message_id

FROM sys.messages

OPEN curMsg

FETCH NEXT FROM  curMsg INTO @MsgId

WHILE @@FETCH_STATUS = 0 BEGIN

     SELECT TOP(1) *

     FROM sys.messages

     WHERE message_id = @MsgId

    

     FETCH NEXT FROM  curMsg INTO @MsgId

    

END

CLOSE curMsg

DEALLOCATE curMsg'

EXEC SP_EXECUTESQL @vcSqlQuery

When cursor variable has declared in the current scope and used in dynamic SQL:

For example:

DECLARE @vcSqlQuery AS NVARCHAR(MAX)

DECLARE @curMsg CURSOR

SET @curMsg = CURSOR STATIC FORWARD_ONLY

FOR

SELECT TOP(5) message_id

FROM sys.messages

OPEN @curMsg

SET @vcSqlQuery = '

DECLARE @MsgId AS BIGINT

FETCH NEXT FROM  @curMsg INTO @MsgId

WHILE @@FETCH_STATUS = 0 BEGIN

     SELECT TOP(1) *

     FROM sys.messages

     WHERE message_id = @MsgId

    

     FETCH NEXT FROM  @curMsg INTO @MsgId

    

END'

EXEC SP_EXECUTESQL @vcSqlQuery,

     N'@curMsg CURSOR',

     @curMsg

    

CLOSE @curMsg

DEALLOCATE @curMsg

When cursor variable has declared in dynamic SQL and used in current scope:

For example:

DECLARE @vcSqlQuery AS NVARCHAR(MAX)

DECLARE @MsgId AS BIGINT

DECLARE @curMsg CURSOR

SET @vcSqlQuery = '

     SET @curMsg = CURSOR STATIC FORWARD_ONLY

     FOR

SELECT TOP(5) message_id

FROM sys.messages;

     OPEN @curMsg'

EXEC SP_EXECUTESQL @vcSqlQuery,

     N'@curMsg CURSOR OUTPUT',

     @curMsg OUTPUT

FETCH NEXT FROM @curMsg INTO @MsgId

WHILE @@FETCH_STATUS = 0 BEGIN

     SELECT TOP(1) *

     FROM sys.messages

     WHERE message_id = @MsgId

    

     FETCH NEXT FROM  @curMsg INTO @MsgId

    

END

     

CLOSE @curMsg

DEALLOCATE @curMsg


Difference between temporary table and table variable in SQL server
Difference between varchar and nvarchar in SQL server.
Difference between char and varchar in SQL server.
Variables in sql server 2008 R2
BETWEEN keyword in sql server