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