In this article, I am giving a quick overview about Dynamic Queries in SQL Server. After completing this article you will understand:
What is Dynamic Queries?
How can we write and Execute Dynamic Queries?
What is difference between EXEC and SP_EXECUTESQL?
What is difference between EXEC and SP_EXECUTESQL?
Please give your valuable suggestions and feedback to improve this article.
What is Dynamic Queries?
Dynamics Queries is nothing but SQL Query which is generated dynamically, stored in a variable and executed on the fly. So I can say Dynamic Queries is a term used to mean SQL code that is generated programatically by your program before it is executed.
How can we write and Execute Dynamic Queries?
Generally in our application we use hard coded SQL queries, but at sometime there is a need to dynamically create SQL Statement; so We build the sql statement as a string, then store it in variable and execute against an active database connection.
There can be many ways to generate Dynamic SQL, but at the end our SQL statements must be correct. If dynamically generated SQL Statement is not correct then it will never work.
There are three ways to execute Dynamic Queries.
1. Write a Query with Parameter
2. Using Exec()
3. Using SP_ExecuteSQL
Write a Query with Parameter
This first approach is very easy to implement, In this approach we pass the parameters values into the WHERE clause of SQL query. The parameters value will be stored in the variable. This is also called as parameterized query.
Let’s first create a Employees table and populate some data then we will understand how does the above approach work?
USE TEACHMESQLSERVER
GO
IF EXISTS ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='EMPLOYEES')
DROP TABLE EMPLOYEES
GO
CREATE TABLE EMPLOYEES(EMPNAME VARCHAR(255), DEPARTMENT VARCHAR(100))
GO
INSERT INTO EMPLOYEES VALUES('RAM','HR')
INSERT INTO EMPLOYEES VALUES('KUMAR','IT')
INSERT INTO EMPLOYEES VALUES('SHYAM','HR')
INSERT INTO EMPLOYEES VALUES('GHANESH','IT')
INSERT INTO EMPLOYEES VALUES('GAUTAM','HR')
INSERT INTO EMPLOYEES VALUES('SUMIT','HR')
INSERT INTO EMPLOYEES VALUES('GOGNA','MARKETING')
GO
SELECT * FROM EMPLOYEES
Let's say we need to find all records from the Employees table where Department is HR. This can be done easily such as the following example shows.
SELECT * FROM EMPLOYEES WHERE DEPARTMENT ='HR'
It can be written as below
DECLARE @DEPARTMENT VARCHAR(255)
SET @DEPARTMENT='HR'
SELECT * FROM EMPLOYEES WHERE DEPARTMENT = @DEPARTMENT
The above query is nothing but a Dynamic Query, which is executed with a parameter. As you can see parameter value is stored in variable and when we pass it into the Dynamic Sql statement we don’t quote it.
Using EXEC
We can build the SQL statement but It doesn't allow parameters like we used in previous example. We have to build the parameter in string and then concatenate it with the SQL statement. It never cache the execution plan but creates the execution plan for each individual query.You will understand what does this means later in this article.
Lets modify the previous query so that we can use it in EXEC.
Lets modify the previous query so that we can use it in EXEC.
DECLARE @DEPARTMENT VARCHAR(255), @DYNAMICQUERY VARCHAR(MAX)
SET @DEPARTMENT='''HR'''
SET @DYNAMICQUERY = 'SELECT * FROM EMPLOYEES WHERE DEPARTMENT ='
EXEC(@DYNAMICQUERY+@DEPARTMENT)
As you can see from this example handling the @department 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.
Using SP_ExecuteSQL
sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql. With this approach you have the ability to still dynamically build the SQL query, but you are also able to still use parameters as you could in example under write query with a parameter. In this approach there is no need to put extra quotes on parameters like we did in previous example. only you have to create sql statement with parameter and store it in variable. In addition, with using this approach you can ensure that the data values being passed into the query are the correct data types.
DECLARE @DEPARTMENT NVARCHAR(255), @QUERY NVARCHAR(MAX)
SET @DEPARTMENT='HR'
SET @QUERY ='SELECT * FROM EMPLOYEES WHERE DEPARTMENT =@DEPARTMENT'
EXEC SP_EXECUTESQL @QUERY ,N'@DEPARTMENT NVARCHAR(255)', @DEPARTMENT=@DEPARTMENT
As you can see from the above example, we have created the dynamic query and stored in @query variable. We are using sp_executesql to execute the dynamic query. we are passing the parameter without quotes.
A little complex example
Suppose I ask you to create a Table name as DynamicTable which has one column name as Query data type is varchar then how will you create it. Yes you are correct, you will write a create table SQL statement as below.
USE TEACHMESQLSERVER
GO
CREATE TABLE DYNAMICTABLE (QUERY VARCHAR(255))
Above SQL statement is a simple SQL statement, which can be run dirtectly.
I am inserting some SQL Statements in DynamicTable table.
INSERT INTO DYNAMICTABLE VALUES ('CREATE TABLE TEMP ('+'DUMMY VARCHAR(MAX))'); -- SQL STATEMENT TO CREATE A TEMP TABLE
INSERT INTO DYNAMICTABLE VALUES ('INSERT INTO TEMP VALUES (''THIS IS INSERTED BY THE FIRST RUN'')'); -- SQL STATEMENT TO INSERT A RECORD IN TEMP TABLE
INSERT INTO DYNAMICTABLE VALUES ('INSERT INTO TEMP VALUES (''THIS IS INSERTED BY THE SECOND RUN'')'); -- SQL STATEMENT TO INSERT A RECORD IN TEMP TABLE
INSERT INTO DYNAMICTABLE VALUES ('INSERT INTO TEMP VALUES (''THIS IS INSERTED BY THE THIRD RUN'')'); -- SQL STATEMENT TO INSERT A RECORD IN TEMP TABLE
GO
SELECT * FROM DYNAMICTABLE
Now what I want is to execute all the SQL Statement present in DynamicTable table. The SQL Statement present in the DynamicTable can be think as Dynamic SQL Statement because it was generated and stored in the table.
Solution
DECLARE @FirstRecord INT , @LastRecord INT, @DynamicQuery VARCHAR (255),@MYRANK INT
SET @FirstRecord=1
SELECT @LastRecord = COUNT(*) FROM DYNAMICTABLE
WHILE @FirstRecord<= @LastRecord
BEGIN
SELECT @DynamicQuery=QUERY,@MYRANK=MYRANK FROM (SELECT QUERY, ROW_NUMBER() OVER(ORDER BY QUERY) AS MYRANKFROM DYNAMICTABLE ) A WHERE MYRANK=@FirstRecord
EXEC(@DynamicQuery)
SET @FirstRecord =@FirstRecord +1
END
GO
SELECT * FROM TEMP
As you can see, our Dynamic query were successfully executed. In first Dynamic Query We created a table name as TEMP and then inserted three records.
What is Difference between Exec and sp_executesql and which one to use?
Exec
· Exec doesn’t allow sql statement to be parameterized, we have to build the string for parameter, therefore it is less secure than sp_executesp in terms of SQL Injection.
· It never cache the execution plan but creates execution plan for each execution this can be advantage in some cases; but it can also be disadvantage in some cases like SQL Server needs to recomplie/ optimize for each execution.
We have already discussed the first point in previous example, now let’s understand the second point with a simple example.
Below I am using the same query I had used for explaining EXEC and SP_EXECUTESQL.
DBCC FREEPROCCACHE -- LET'S CLEAR THE EXECUTION PLAN FROM BUFFER
-- LET'S Execute the Below query
DECLARE @DEPARTMENT VARCHAR(255), @DYNAMICQUERY VARCHAR(MAX)
SET @DEPARTMENT='''IT'''
SET @DYNAMICQUERY = 'SELECT * FROM EMPLOYEES WHERE DEPARTMENT ='
EXEC(@DYNAMICQUERY+@DEPARTMENT)
--To execute the above query database engine will create the execution plan, let’s check the Chached Execution plan for above query.
SELECT A.TEXT,*
FROM SYS.DM_EXEC_CACHED_PLANS B
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(B.PLAN_HANDLE) A
WHERE A.TEXT LIKE '%SELECT * FROM EMPLOYEES WHERE DEPARTMENT %'
AND A.TEXT NOT LIKE '%SELECT A.TEXT%'
-- LET'S Execute the Below query with different parameter value
DECLARE @DEPARTMENT VARCHAR(255), @DYNAMICQUERY VARCHAR(MAX)
SET @DEPARTMENT='''HR'''
SET @DYNAMICQUERY = 'SELECT * FROM EMPLOYEES WHERE DEPARTMENT ='
EXEC(@DYNAMICQUERY+@DEPARTMENT)
--To execute the above query database engine again will create the execution plan, let’s check the Cached Execution plan for second query.
SELECT A.TEXT,*
FROM SYS.DM_EXEC_CACHED_PLANS B
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(B.PLAN_HANDLE) A
WHERE A.TEXT LIKE '%SELECT * FROM EMPLOYEES WHERE DEPARTMENT %'
AND A.TEXT NOT LIKE '%SELECT A.TEXT%'
As you can see database engine is creating execution plan for each execution.you can see in the Text column table has one record for HR parameter and another record for IT parameter.
SP_executesql
· sp_executesql allows for statements to be parameterized, therefore it is more secure than EXEC in terms of SQL Injection.
· TSQL string is build one time, when it get executed first time execution plan created by Database engine will be cached. When we execute it next time it uses that same execution plan, It never Creates second Execution Plan it. This can be advantage and disadvantage, so you have to decide what to use to execute dynamic query based on your requirement.
We have already discussed the first point in previous example, now let’s understand the second point with a simple example.
DBCC FREEPROCCACHE -- LET'S CLEAR THE EXECUTION PLAN FROM BUFFER
-- LET'S Execute the Below query
DECLARE @DEPARTMENT NVARCHAR(255), @QUERY NVARCHAR(MAX)
SET @DEPARTMENT='IT'
SET @QUERY ='SELECT * FROM EMPLOYEES WHERE DEPARTMENT =@DEPARTMENT'
EXEC SP_EXECUTESQL @QUERY ,N'@DEPARTMENT NVARCHAR(255)', @DEPARTMENT=@DEPARTMENT
--To execute the above query database engine will create the execution plan, let’s check the Cached Execution plan for above query.
SELECT A.TEXT,*
FROM SYS.DM_EXEC_CACHED_PLANS B
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(B.PLAN_HANDLE) A
WHERE A.text like '%SELECT * FROM EMPLOYEES WHERE DEPARTMENT %'
and A.TEXT NOT LIKE '%SELECT A.TEXT%'
-- LET'S Execute the Below query with different parameter value
DECLARE @DEPARTMENT NVARCHAR(255), @QUERY NVARCHAR(MAX)
SET @DEPARTMENT='HR'
SET @QUERY ='SELECT * FROM EMPLOYEES WHERE DEPARTMENT =@DEPARTMENT'
EXEC SP_EXECUTESQL @QUERY ,N'@DEPARTMENT NVARCHAR(255)', @DEPARTMENT=@DEPARTMENT
--To execute the above query database engine will not create the execution plan. It will use the same execution plan which was used to run the query first time. let’s check and confirm the Chached Execution plan for second query.
SELECT A.TEXT,*
FROM SYS.DM_EXEC_CACHED_PLANS B
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(B.PLAN_HANDLE) A
WHERE A.text like '%SELECT * FROM EMPLOYEES WHERE DEPARTMENT %'
and A.TEXT NOT LIKE '%SELECT A.TEXT%'.
As you can see it didn’t not create another execution plan, it used the old execution plan. You can also see object type for this plan is prepared.
As we all know that the dynamic SQL is not good where performance is concern. But sometimes we don't have other choices. As I personally think that, no developer chooses the dynamic SQL with interest, but they choose it as they have no alternates to minimize the code. Well the debugging of dynamic SQL is not so easy.
Dynamic SQL result stored in a TABLE
Introduction
Some times when the developer works with stored procedure they want to make certain dynamic SQL and want to copy the output or the result set of the dynamic SQL into a table and next want to work with table for farther processing.
Please note that the Temporary Table is not permitted here.
Please note that the Temporary Table is not permitted here.
The Problem
As the columns name and data type of the dynamic SQL is dynamic, it is not possible to make a table definition within the stored procedure.
To understand it properly here I am providing an example.
-- Base Table Object Creation
IF OBJECT_ID('my_EMP') IS NOT NULL
BEGIN
DROP TABLE my_EMP;
END
GO
CREATE TABLE my_EMP
(EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPGRADE VARCHAR(1) NOT NULL,
EMPDEPT VARCHAR(50) NOT NULL,
EMPCITY VARCHAR(50) NOT NULL,
EEMSTATE VARCHAR(50) NOT NULL);
GO
-- Inserting some Records
INSERT INTO my_EMP
(EMPNAME, EMPGRADE, EMPGRADE, EMPDEPT, EMPCITY, EEMSTATE)
VALUES ('JOYDEEP DAS', 'B', 'DEV', 'AGARTALA', 'TRIPURA'),
('TUHIN SHINAH', 'B', 'DEV', 'KOLKATA', 'WEST BENGAL'),
('SANGRAM JIT', 'B', 'DEV', 'KOLKATA', 'WEST BENGAL'),
('SUKAMAL JANA', 'B', 'DEV', 'CHUCHURA', 'WEST BENGAL'),
('SUDIP DAS', 'A', 'MGR', 'KOLKATA', 'WEST BENGAL'),
('SAIKAT SREE', 'A', 'MGR', 'KOLKATA', 'WEST BENGAL'),
('MANI SANKAR', 'C', 'DM', 'AGARTALA', 'TRIPURA'),
('A DEKA', 'C', 'DM', 'GWAHATI', 'ASSAM');
GO
-- Creting Stored Procedure with Dynamic SQL
-- Note that the parameters, one is the fields name and second is the Table objects
-- So the Dynamic Sql fields and table are dynamic and depends on user input.
IF OBJECT_ID('usp_DISPLAYEMP') IS NOT NULL
BEGIN
DROP PROCEDURE usp_DISPLAYEMP;
END
GO
CREATE PROCEDURE usp_DISPLAYEMP
(
@p_Attributes VARCHAR(200) = NULL,
@p_TblName VARCHAR(50) = NULL
)
AS
DECLARE @sqlString VARCHAR(200);
BEGIN
IF ISNULL(@p_Attributes, '')<>'' AND ISNULL(@p_TblName, '')<>''
BEGIN
SET @sqlString='SELECT '+ @p_Attributes + ' FROM '+ @p_TblName;
EXEC (@sqlString);
END
ELSE
BEGIN
PRINT 'Input can not be Null';
END
END
-- Executing the SP [ with Fields name EMPNAME, EMPGRADE, EMPGRADE ]
EXEC usp_DISPLAYEMP
@p_Attributes = 'EMPNAME, EMPGRADE, EMPGRADE',
@p_TblName = 'my_EMP'
The output is
EMPNAME EMPGRADE EMPGRADE
-------------------------------------------------- -------- --------
JOYDEEP DAS B B
TUHIN SHINAH B B
SANGRAM JIT B B
SUKAMAL JANA B B
SUDIP DAS A A
SAIKAT SREE A A
MANI SANKAR C C
A DEKA C C
(8 row(s) affected)
-- Executing the SP [ with Fields All Fields Name ]
EXEC usp_DISPLAYEMP
@p_Attributes = '*',
@p_TblName = 'my_EMP'
The Output is
EMPID EMPNAME EMPGRADE EMPDEPT EMPCITY EEMSTATE
----------------------------- ---------------------------------------
1 JOYDEEP DAS B DEV AGARTALA TRIPURA
2 TUHIN SHINAH B DEV KOLKATA WEST BENGAL
3 SANGRAM JIT B DEV KOLKATA WEST BENGAL
4 SUKAMAL JANA B DEV CHUCHURA WEST BENGAL
5 SUDIP DAS A MGR KOLKATA WEST BENGAL
6 SAIKAT SREE A MGR KOLKATA WEST BENGAL
7 MANI SANKAR C DM AGARTALA TRIPURA
8 A DEKA C DM GWAHATI ASSAM
(8 row(s) affected)
So in this example the output of the stored procedure depends on user input. We can't understand how many columns it displays.
We want to use a table within this stored procedure.
Easy Solutions
Example-1
-- Using Temp Table
IF OBJECT_ID('usp_DISPLAYEMP') IS NOT NULL
BEGIN
DROP PROCEDURE usp_DISPLAYEMP;
END
GO
CREATE PROCEDURE usp_DISPLAYEMP
(
@p_Attributes VARCHAR(200) = NULL,
@p_TblName VARCHAR(50) = NULL
)
AS
DECLARE @sqlString VARCHAR(200);
BEGIN
IF ISNULL(@p_Attributes, '')<>'' AND ISNULL(@p_TblName, '')<>''
BEGIN
SET @sqlString='SELECT '+ @p_Attributes +
' INTO TMP_TBL FROM '+@p_TblName;
' INTO TMP_TBL FROM '+@p_TblName;
EXEC (@sqlString);
END
ELSE
BEGIN
PRINT 'Input can not be Null';
END
END
Example-2
-- Using Temp Table
IF OBJECT_ID('usp_DISPLAYEMP') IS NOT NULL
BEGIN
DROP PROCEDURE usp_DISPLAYEMP;
END
GO
CREATE PROCEDURE usp_DISPLAYEMP
(
@p_Attributes VARCHAR(200) = NULL,
@p_TblName VARCHAR(50) = NULL
)
AS
DECLARE @sqlString VARCHAR(200);
BEGIN
IF ISNULL(@p_Attributes, '')<>'' AND ISNULL(@p_TblName, '')<>''
BEGIN
SET @sqlString='SELECT '+ @p_Attributes +
' INTO ' + TMP_TBL +
' FROM '+ @p_TblName;
EXEC (@sqlString);
END
ELSE
BEGIN
PRINT 'Input can not be Null';
END
END
Share your Knowledge
If we can put the output in a Temporary Table (the Local Temporary Table) it well is the BEST.
But in this situation it is NOT possible. If have you an idea, please share your knowledge with us.
Related Tropics
Dynamic SQL With sp_executesql ()
Hope you like it.
Dynamic SQL With sp_executesql ()
Dynamic SQL is a part of the development. It is not a good idea to use the dynamic SQL. But the real facts are that, in such kind of situation we must use the dynamic SQL.
Here in this article I am trying to illustrate the execution of dynamic SQL.
We can execute the dynamic SQL by using
1. EXECUTE statements
2. By using SP_EXECUTESQL () stored procedure.
Microsoft always recommended that to execute the dynamic SQL we must use the stored procedure SP_EXECUTESQL ().
Why we are going to use SP_EXECUTESQL ()
First we take an example:
CREATE PROC sp_sample
@v_tblName sysname
AS
DECLARE @strQuery nVARCHAR(4000)
SELECT @strQuery = 'SELECT * FROM DBO.' + QUOTENAME(@v_tblName)
EXEC SP_EXECUTESQL() @strQuery -------- (Statement -A)
EXEC (@strQuery) -------- (Statement -B)
GO
In this example both Statement-A and Statement-B gives us the same output.
But for Statement-A, we must declare the @strQuery as nVARCHAR, nCHAR or nTEXT others it gives us the following error.
Msg 214, Level 16, State 2, Procedure sp_SP_EXECUTESQL() , Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
The EXEC is Un-parameterized and SP_EXECUTESQL () is parameterized, that means if we write a query which takes a parameter like "EmpID". When we run the query with "EmpID" as 1 and 2 it would be creating two different cache entries (one each for value 1 and 2 respectively).
It means for Un-parameterized queries the cached plan is reused only if we ask for the same id again. So the cached plan is not of any major use.
But in case of SP_EXECUTESQL () the similar situation for "Parameterised" queries the cached plan would be created only once and would be reused 'n' number of times. So this would have better performance.
It means for Un-parameterized queries the cached plan is reused only if we ask for the same id again. So the cached plan is not of any major use.
But in case of SP_EXECUTESQL () the similar situation for "Parameterised" queries the cached plan would be created only once and would be reused 'n' number of times. So this would have better performance.
I am trying to explain it by an example
CREATE TABLE my_emp
(EmpID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EmpName VARCHAR(50)NOT NULL)
GO
INSERT INTO my_emp (EmpName)
VALUES('Joydeep Das'),
('Sukamal jana'),
('Sudip Das'),
('Tuhin Shinah')
CREATE TABLE my_empGarde
(EmpID INT NOT NULL PRIMARY KEY,
EmpGrade VARCHAR(50)NOT NULL,
FOREIGN KEY (EmpID) REFERENCES my_emp(EmpID))
GO
INSERT INTO my_empGarde (EmpID, EmpGrade)
VALUES (1, 'A'),
(2, 'A'),
(3, 'A+'),
(4, 'B')
GO
DBCC Freeproccache
/*
Here DBCC FREEPROCCACHE to clear the procedure cache.
Freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled
instead of reused from the cache.
*/
DECLARE @v_SQLTxt nVARCHAR(1000)
SET @v_SQLTxt='SELECT a.EmpID, a.EmpName, b.EmpGrade
FROM my_emp a
INNER JOIN my_empGarde b ON a.EmpID = b.EmpID
WHERE a.EmpID = N''1'''
EXEC (@v_SQLTxt)
SET @v_SQLTxt='SELECT a.EmpID, a.EmpName, b.EmpGrade
FROM my_emp a
INNER JOIN my_empGarde b ON a.EmpID = b.EmpID
WHERE a.EmpID = N''2'''
EXEC (@v_SQLTxt)
SET @v_SQLTxt='SELECT a.EmpID, a.EmpName, b.EmpGrade
FROM my_emp a
INNER JOIN my_empGarde b ON a.EmpID = b.EmpID
WHERE a.EmpID = @EmpID'
Exec sp_executesql @v_SQLTxt, N'@EmpID int', 1
Exec sp_executesql @v_SQLTxt, N'@EmpID int', 2
/*
After this lets have a look at the cached plan by executing the below query.
The first two (Unparameterised) has a execution_count of 1,
the last one (Parameterised) would have an execution_count of 2.
*/
SELECT sqlTxt.text, qStats.execution_count
FROM sys.dm_exec_query_stats qStats
CROSS APPLY (SELECT [text]
FROM sys.dm_exec_sql_text(qStats.sql_handle)) assqlTxt option (Recompile)
