sql-server-2008 - SQL Server 在存储过程中使用 EXEC/sp_executesql 还是只是普通的 sql？

Question

我有一个简单的存储过程，为实现最佳查询缓存/优化而编写代码的最佳方式是什么？

我目前的情况如下。

ALTER PROCEDURE dbo.OccupierGet

(
@OccupierID int = 0
)

AS
/* SET NOCOUNT ON */
--Get all details that can be editted.
select TOP 1 ID,AccountNumber,FirstName,LastName,Company,Telephone,Notes,
OccupierTypeID,Address,Address2,City,Country,Telephone2,HomePhone,CellPhone,WorkPhone,Fax,EmailAddress 
from dbo.Occupier
where ID = @OccupierID

RETURN

将 sql 查询构建为字符串并使用 sp_executesql 运行并传递参数会更好吗？我问是因为查询缓存和我正在使用的参数。

谢谢!蒂姆

Answer 1

没有理由在这里使用动态 SQL。当您确实需要使用动态 SQL 时，您应该考虑 sp_executesql 优先于 EXEC()。有多种原因，包括:

1. sp_executesql 更有可能重用查询计划(参见 Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL) )；

2. 将强类型参数传递到 sp_executesql 中要容易得多(阻止 SQL 注入(inject)比连接字符串更好)；并且，

3. 您还可以从动态 SQL 范围内获取变量返回调用范围，例如:

DECLARE @i INT, @sql NVARCHAR(MAX), @dbname SYSNAME = N'model';

SET @sql = N'SELECT @i = COUNT(*) FROM ' 
    + @dbname + '.sys.tables;' 

EXEC sp_executesql @sql, N'@i INT OUTPUT', @i = @i OUTPUT;

PRINT @i;

这不是一个非常有用的示例，但它是执行动态字符串时的常见问题。但更重要的是，您应该只在必要时才考虑使用动态 SQL，而不是将其作为首选。