Dynamic SQL Query in SQL Server and Why We Need This

Introduction

Before Dive into the Topic of Dynamic SQL query lets have a look into Static query and why we need dynamic SQL query even though we have Static query .
Lets Get Started....
See the below Image which describes the SQL Queries types based upon the execution.
Basically we will discuss more about Dynamic SQL rather than Static SQL.

SQL Queries

It can be divided into 2 types.

• Static SQL Query

• Dynamic SQL query

Static SQL Query

Static SQL query is known at compile time i.e. Compiler have the idea about the SQL query .
This is straight forward what we use our day to day life. See the below example.

SELECT * FROM TESTINSERT t WHERE t.NAME='Rahul'

We are not warping the SQL Query as a string.  

Dynamic SQL query

Dynamic SQL query is not known at compile time i.e. Compiler do not have any idea about the SQL query .
This is quite different than the normal SQL Statement as this can not be executed straight forward.
This dynamic SQL query wrap in a string format and get executed by using below commands.

• EXECUTE(<string value>)

• EXECUTE SP_EXECUTESQL(<string value>)

We will discuss the above two command this article.Lets discuss Dynamic SQL with an example.

DECLARE @Statement NVARCHAR(200)DECLARE @Name VARCHAR(10)

SET @Statement ='SELECT * FROM TESTINSERT t WHERE t.NAME=''' + @Name''''EXECUTE(@Statement )

The above is the example of Dynamic SQL as it stored in a string variable called @Statement and in the next step it execute the string to get the output by using EXECUTE Command.

Lets Dig into the Topic and Understand the topic more deeper.

1. EXECUTE SP_EXECUTESQL,<SQLstring>, <datatype>,parametervalue
2. EXECUTE(<SQL String>)

Lets Understand the below example.

Procedure-1(Execute)

--EXECUTE [PROCDynamicQueryExecute]
ALTER PROCEDURE [dbo].[PROCDynamicQueryExecute]
  AS
  BEGIN
  DECLARE @ID INT
  SET @ID=1
  DECLARE @FullQuery NVARCHAR(500)
  SET @FullQuery = N'SELECT * FROM TESTINSERT WHERE id= ' + CAST(@ID AS                VARCHAR) EXECUTE(@FullQuery)
END

Procedure-2  (Execute sp_executesql)

--EXEC [PROCDynamicQuery_ExecuteSQL]

ALTER PROCEDURE [dbo].[PROCDynamicQuery_ExecuteSQL]

AS

BEGIN

DECLARE @ID INT

SET @ID=1

DECLARE @QryName NVARCHAR(100)

set @QryName=N'ID=@ID'

DECLARE @str Nvarchar(100)

SET @str = N'@ID INT'

DECLARE @FullQuery NVARCHAR(500)

SET @FullQuery = N'SELECT * FROM TESTINSERT WHERE id= @ID'

EXECUTE sp_Executesql @FullQuery,@str,@ID=1

END

See the two procedure(Procedure1 and Procedure2) Carefully and lets find the difference between these two.

Difference 1

In Procedure1 we can not pass the value to the Execute(not allow parameter) where as we can pass the value in Procedure2(parameterized). See the below command.

1. EXECUTE(@FullQuery)
2. EXECUTE sp_Executesql @FullQuery,@str,@ID=1

Difference 2

We  can apply Execute Sp_ExecuteSQL in place of Execute .

Example- EXECUTE sp_Executesql @FullQuery

Difference 3

Risk of SQL injection is more in case of Execute where as less in Execute SP_executesql.

Difference 4

Plan not cached in Execute where as Plan cached in Execute SP_executesql. at 1st execution.

Note

Both the dynamic query building technique helps to build the dynamic SQL. So that even if we do not have any idea about the value in compile time we can can execute the query using Dynamic SQL with the help of the 2 command above discussed. We normally use dynamic query when value is known at run time.

Thanks.....................................

Share: