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 MYRANK FROM
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.
I hope you enjoyed the article, don't forget to share this article on facebook.
I hope you enjoyed the article, don't forget to share this article on facebook.
No comments:
Post a Comment