Feb 3, 2013

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

No comments:

Post a Comment