Google+ Followers

Wednesday, September 3, 2014

Dynamic SQL in SQL Server

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?

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.

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.

No comments: