What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using
EXEC (@SQL)
versus
EXEC SP_EXECUTESQL @SQL
?
Add a comment
|
5 Answers
sp_executesql is more likely to promote query plan reuse. When using sp_executesql, parameters are explicitly identified in the calling signature. This excellent article descibes this process.
The oft cited reference for many aspects of dynamic sql is Erland Sommarskog's must read: "The Curse and Blessings of Dynamic SQL".
1 Comment
variable
Please can you add reason why sp_executesql allows query plan re-use where as exec does not?
The big thing about SP_EXECUTESQL is that it allows you to create parameterized queries which is very good if you care about SQL injection.
2 Comments
DJ.
I don't think you can parameteize a dynamic sql without it??
Steve Rogers
Absolutely one of the biggest reasons to use sp_executesql if building the query dynamically to prevent sql injection.
Microsoft's Using sp_executesql article recommends using sp_executesql instead of execute statement.
Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTE.
So, the take away: Do not use execute statement. Use sp_executesql.
3 Comments
OzrenTkalcecKrznaric
Your takeaway does not stand always. There are occasions when there is no efficiency bonus by using sp_executesql, but you can protect your code from sql injection attack. Sometimes you just can't use sp_executesql the way you can use exec, so... someone said - there is no silver bullet. I agree.
Gan
Yes, Microsoft should have put it as "more likely to be efficient". And being in the industry for some years, I have seen cases where
sp_executesql cannot be used to replace execute. Perhaps I should put the point I am trying to stress as: Use sp_executesql instead of execute whenever possible.variable
Please can you add reason why sp_executesql allows query plan re-use where as exec does not?
I would always use sp_executesql these days, all it really is is a wrapper for EXEC which handles parameters & variables.
However do not forget about OPTION RECOMPILE when tuning queries on very large databases, especially where you have data spanned over more than one database and are using a CONSTRAINT to limit index scans.
Unless you use OPTION RECOMPILE, SQL server will attempt to create a "one size fits all" execution plan for your query, and will run a full index scan each time it is run.
This is much less efficient than a seek, and means it is potentially scanning entire indexes which are constrained to ranges which you are not even querying :@
Comments
- Declare the variable
- Set it by your command and add dynamic parts like use parameter values of sp(here @IsMonday and @IsTuesday are sp params)
execute the command
declare @sql varchar (100) set @sql ='select * from #td1' if (@IsMonday+@IsTuesday !='') begin set @sql= @sql+' where PickupDay in ('''+@IsMonday+''','''+@IsTuesday+''' )' end exec( @sql)
3 Comments
Erik A. Brandstadmoen
This is open to SQL injection, if you put e.g. "a';DROP DATABASE DATABASE_NAME; GO;';" in the @IsMonday variable
Vikas Rana
is it prone to sql injuction if @IsMonday is int ?
Weihui Guo
@VikasRana @IsMonday cannot be
int in dynamic SQL. Note that @sql is declared as varchar or nvarchar