动态 SQL - EXEC(@SQL) 与 EXEC SP_EXECUTESQL(@SQL)

2021-12-02 00:00:00 sql dynamic sql-server

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

What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using

EXEC (@SQL)

对比

EXEC SP_EXECUTESQL @SQL

?

推荐答案

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

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.

关于动态 sql 的许多方面经常被引用的参考是 Erland Sommarskog 的必读:The Curse and Blessings of动态 SQL".

The oft cited reference for many aspects of dynamic sql is Erland Sommarskog's must read: "The Curse and Blessings of Dynamic SQL".

相关文章