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,

Skip to main content