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.

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)) as sqlTxt option (Recompile)

                             

Hope you like it.

Posted by: MR. JOYDEEP DAS