104

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

?

MIWMIB
  • 1,407
  • 1
  • 14
  • 24
Ash Machine
  • 9,601
  • 11
  • 45
  • 52

5 Answers5

102

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".

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
24

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.

DJ.
  • 16,045
  • 3
  • 42
  • 46
5

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.

Gan
  • 4,827
  • 3
  • 34
  • 50
  • 7
    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. – OzrenTkalcecKrznaric Jun 17 '13 at 09:45
  • 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*. – Gan Jun 17 '13 at 17:00
  • Please can you add reason why sp_executesql allows query plan re-use where as exec does not? – variable Nov 25 '21 at 14:52
2

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 :@

Ten98
  • 772
  • 1
  • 6
  • 9
-2
  1. Declare the variable
  2. Set it by your command and add dynamic parts like use parameter values of sp(here @IsMonday and @IsTuesday are sp params)

  3. 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)
Sara
  • 37
  • 1