动态 SQL - EXEC(@SQL) 与 EXEC SP_EXECUTESQL(@SQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/548090/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)
提问by Ash Machine
What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using
使用 SQL Server 在存储过程中执行动态 SQL 命令的现实世界的优缺点是什么？
EXEC (@SQL)
versus
相对
EXEC SP_EXECUTESQL @SQL
?
?
回答by Mitch Wheat
sp_executesqlis 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.
sp_executesql更有可能促进查询计划重用。使用时sp_executesql，参数在调用签名中明确标识。这篇优秀的文章描述了这个过程。
The oft cited reference for many aspects of dynamic sql is Erland Sommarskog's must read: "The Curse and Blessings of Dynamic SQL".
有关动态 sql 的许多方面经常被引用的参考资料是 Erland Sommarskog 的必读物：“动态 SQL 的诅咒和祝福”。
回答by DJ.
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.
SP_EXECUTESQL 的重要之处在于它允许您创建参数化查询，如果您关心 SQL 注入，这是非常好的。
回答by Gan
Microsoft's Using sp_executesqlarticle recommends using sp_executesqlinstead of executestatement.
微软的Using sp_executesql文章推荐使用sp_executesql而不是execute语句。
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 efficientthan EXECUTE.
因为这个存储过程支持参数替换，所以 sp_executesql 比 EXECUTE 更通用；并且因为 sp_executesql 生成的执行计划更有可能被 SQL Server 重用，所以 sp_executesql比 EXECUTE更有效。
So, the take away: Do not use executestatement. Use sp_executesql.
所以，要点是：不要使用executestatement。使用sp_executesql.
回答by Ten98
I would always use sp_executesql these days, all it really is is a wrapper for EXEC which handles parameters & variables.
这些天我总是使用 sp_executesql，它实际上只是处理参数和变量的 EXEC 的包装器。
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.
但是，在非常大的数据库上调整查询时不要忘记 OPTION RECOMPILE，尤其是当数据跨越多个数据库并使用 CONSTRAINT 来限制索引扫描时。
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.
除非您使用 OPTION RECOMPILE，否则 SQL Server 将尝试为您的查询创建一个“一刀切”的执行计划，并且每次运行时都会运行完整的索引扫描。
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 :@
这比查找效率低得多，这意味着它可能会扫描整个索引，这些索引限制在您甚至没有查询的范围内：@
回答by Sara
- 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)
- 声明变量
- 通过您的命令设置它并添加动态部分，例如使用 sp 的参数值（这里 @IsMonday 和 @IsTuesday 是 sp 参数）
执行命令
declare @sql varchar (100) set @sql ='select * from #td1' if (@IsMonday+@IsTuesday !='') begin set @sql= @sql+' where PickupDay in ('''+@IsMonday+''','''+@IsTuesday+''' )' end exec( @sql)
