Dynamic SQL - EXEC SQL versus EXEC SP EXECUTESQL SQL

Question

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

User · Answer

I would always use sp executesql these days  all it really is is a wrapper for EXEC which handles parameters  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

Examples related to sql

Examples related to sql-server

Examples related to dynamic