动态SQL - EXEC(@SQL)与EXEC SP_EXECUTESQL(@SQL)的比较

Question

在SQL Server中使用存储过程执行动态SQL命令的现实世界的优缺点是什么？

EXEC (@SQL)

对比

EXEC SP_EXECUTESQL @SQL

?

- Ash Machine

Answer 1

sp_executesql 更有可能促进查询计划的重用。使用sp_executesql时，参数在调用签名中被明确定义。这篇优秀的文章描述了这个过程。

许多动态 SQL 方面的常引用参考是 Erland Sommarskog 的必读之作：" The Curse and Blessings of Dynamic SQL"。

- Mitch Wheat

---

请问您能否解释一下为什么 sp_executesql 允许查询计划重用，而 exec 不允许呢？ - variable

Answer 2

SP_EXECUTESQL的重要之处在于它允许您创建参数化查询，如果您关心SQL注入，这非常有用。

- DJ

---

我认为你不能在没有它的情况下将动态SQL参数化吗？ - DJ.

使用sp_executesql构建动态查询的最大原因之一是防止SQL注入。 - Steven Rogers

Answer 3

Microsoft的使用sp_executesql文章建议使用sp_executesql而不是execute语句。

由于此存储过程支持参数替换，因此sp_executesql比EXECUTE更具有灵活性； 而且，由于sp_executesql生成的执行计划更可能被SQL Server重用，所以它比EXECUTE更高效。

因此，要点是：不要使用execute语句。使用sp_executesql。

- Gan

---

你的收获并不总是站得住脚。有时使用sp_executesql没有效率奖励，但可以保护你的代码免受SQL注入攻击。有时你不能像使用exec那样使用sp_executesql，所以......有人说- 没有万能药。我同意。 - OzrenTkalcecKrznaric

是的，微软应该将其表述为“更有可能高效”。作为业内人士多年，我见过一些情况下无法使用sp_executesql替换execute。也许我应该强调的重点是：尽可能使用sp_executesql而不是execute。 - Gan

请问您能否解释一下为什么 sp_executesql 允许查询计划重用，而 exec 不允许呢？ - variable

Answer 4

我现在通常会使用sp_executesql，它实际上只是EXEC的包装器，可以处理参数和变量。

但是，在调整特别大的数据库查询时，请不要忘记使用OPTION RECOMPILE，尤其是在您的数据跨越多个数据库并且正在使用约束来限制索引扫描的情况下。

除非您使用OPTION RECOMPILE，否则SQL服务器将尝试为您的查询创建“一刀切”的执行计划，并在每次运行时运行完整的索引扫描。

这比搜索效率低得多，这意味着它有可能扫描整个索引，而这些索引仅受到您未查询的范围的限制。 :@

- Ten98

Answer 5

1. 声明变量
2. 根据您的指令设置它，并添加动态部分，例如使用sp参数值（此处@IsMonday和@IsTuesday为sp参数）

3. 执行指令

   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

---

如果在@IsMonday变量中输入例如"a';DROP DATABASE DATABASE_NAME; GO;';"，则存在SQL注入的风险。 - Erik A. Brandstadmoen

如果@IsMonday是int类型，它是否容易受到SQL注入攻击？ - Vikas Rana

@VikasRana @IsMonday 在动态 SQL 中不能作为 int。请注意，@sql 声明为 varchar 或 nvarchar。 - Weihui Guo