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
- sp_executesql allows for statements to be parameterized
- Therefore It’s more secure than EXEC in terms of SQL injection
- 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
- 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%’
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%’
2 different parameter sets used same query plan because as you can see cached query plan is parameterized.
Great
Good Explanation. thanks
Precise and clear explanations. Thank You.
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
Thank you.
It's a great explanations.
Thank you sir.
Really you are genius.
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!
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,