EXEC vs. sp_executeSQL


When we want to execute a TSQL string we can use both EXEC and sp_executesql statements. But there are some very important differences between them

  1. sp_executesql allows for statements to be parameterized
    • Therefore It’s more secure than EXEC in terms of SQL injection
  2. sp_executesql can leverage cached query plans.
    • The TSQL string is built only one time, after that every time same query is called with sp_executesql, SQL Server retrieves the query plan from cache and reuses it
  3. Temp tables created in EXEC can not use temp table caching mechanism

Let’s make a demo to see above number 2 behavior

Use AdventureWorks2012

GO

 

–DO NOT RUN this script on Production environment

–Clear the plan cache

dbcc freeproccache

 

–Use EXEC to execute a TSQL string

declare @str varchar(max)=,

            @param1 varchar(50)=,

            @param2 varchar(50)=

set @param1=‘1’

set @param2=‘2’

set @str=‘select * from Person.Address where AddressID in (‘+@param1+‘,’+@param2+‘)’

exec(@str)

 

–Execute the same query with different paramaters

declare @str varchar(max)=,

            @param1 varchar(50)=,

            @param2 varchar(50)=

set @param1=‘3’

set @param2=‘4’

set @str=‘select * from Person.Address where AddressID in (‘+@param1+‘,’+@param2+‘)’

exec(@str)

 

–Look at the cached query plans

select st.text,*

from sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

where (st.text like ‘%select * from Person.Address%’)

and st.text not like ‘%select st.text%’

 

image

As you see 2 different query plans(1 for each query) are cached. Because EXEC does not allow for statements to be parameterized. They are similar to ad-hoc queries.

Let’s do same example with sp_executesql

–Let’s do same example with sp_executesql

 

Use AdventureWorks2012

GO

 

–DO NOT RUN this script on Production environment

–Clear the plan cache

dbcc freeproccache

 

sp_executesql 1

declare @param1 int,

          @param2 int

set @param1=1

set @param2=2

exec sp_executesql N’select * from Person.Address where AddressID in (@1,@2)’

            ,N’@1 int, @2 int

            ,@param1, @param2

           

sp_executesql 2

declare @param1 int,

          @param2 int

set @param1=3

set @param2=4

exec sp_executesql N’select * from Person.Address where AddressID in (@1,@2)’

            ,N’@1 int, @2 int

            ,@param1, @param2

           

–Look at the cached query plans

select st.text,*

from sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

where (st.text like ‘%select * from Person.Address%’)

and st.text not like ‘%select st.text%’       

  

image

2 different parameter sets used same query plan because as you can see cached query plan is parameterized.

Comments (8)

  1. Sreenivas says:

    Good Explanation. thanks

  2. Sameer says:

    Precise and clear explanations. Thank You.

  3. Debo says:

    You mentioned sp_executesql is more secure than EXEC.  Does this hold true for the following?

    EXEC iReturn = sp_executesql N'SELECT @x = 1','@x int OUTPUT', @x OUTPUT

  4. Jesse says:

    Thank you.

    It's a great explanations.

  5. Abu Hena Mostofa Zamal says:

    Thank you sir.

    Really you are genius.

  6. Bruno Dias says:

    Thank you Sahtiyan,

    I was looking for solution to delete the dynamic mode data. I adapted your example and build this frankstein:

    CREATE Procedure [dbo].[ZAPSQL]

    @Table VarChar(40),  

    @UserAtt VarChar(50),

    @User VarChar(30)

    As  

    Declare @SQLQuery AS NVarchar(4000)

       Declare @ParamDefinition AS NVarchar(2000)

       /* Build the Transact-SQL String with the input parameters, concatenating Table and Attribute than are dynamics */

       Set @SQLQuery = 'Delete From ' + @Table + ' where ' + @UserAtt + ' = @User'

       /* Specify Parameter Format for all input parameters included in the stmt */

       Set @ParamDefinition = '@User Varchar(30)'

       /* Execute the Transact-SQL String with all parameter value's

          Using sp_executesql Command */

       Execute sp_Executesql @SQLQuery,

                             @ParamDefinition,

                             @user

    This works!

  7. Neeraj Kumar says:

    Hi Sahtiyan,

    I have 50000 csv files each having millions of record.I want to import these files into a sql table.

    I am using SQL Bulk Insert to perform  this operation but it is taking nearly 10 day to complete.

    What is the fastest way to perform this operation?

    Thanks,