SQL Server 在存储过程中使用 EXEC/sp_executesql 还是只使用普通的 sql? [英] SQL Server use EXEC/sp_executesql or just plain sql in stored procedure?

问题描述

我有一个简单的 sproc，为实现最佳查询缓存/优化而对其进行编码的最佳方法是什么?

我目前的情况如下.

ALTER PROCEDURE dbo.OccupierGet(@OccupierID int = 0)作为/* 设置无计数 */--获取所有可以编辑的详细信息.选择前 1 个 ID、帐号、名字、姓氏、公司、电话、备注，占用者类型 ID、地址、地址 2、城市、国家、电话 2、家庭电话、手机、工作电话、传真、电子邮件地址来自 dbo.Occupier其中 ID = @OccupierID返回

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

谢谢！提姆

解决方案

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

1. sp_executesql 更有可能重用查询计划(参见 动态 SQL - EXEC(@SQL) 与 EXEC SP_EXECUTESQL(@SQL));

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

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;打印@i;

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

I have a simple sproc, what is the best way to code it for best query caching/optimization?

I currently have it as follows.

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

Would it be better to build the sql query as a string and run with sp_executesql and pass the parameter? I'm asking because of query caching and the parameter I'm using.

Thank you! Tim

解决方案

See no reason to use dynamic SQL here. When you do need to use dynamic SQL, you should consider sp_executesql higher in preference than EXEC(). There are a variety of reasons, including:

1. sp_executesql is more likely to reuse query plans (see Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL));

2. it is much easier to pass strongly-typed parameters into sp_executesql (thwarting SQL injection better than concatenating a string); and,

3. you can also get variables from within the dynamic SQL scope back out to the calling scope, for example:

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;

That's not a very useful example, but it is a common problem when executing dynamic strings. But more to the point, you should only be considering dynamic SQL when you have to, not as a first resort.

这篇关于SQL Server 在存储过程中使用 EXEC/sp_executesql 还是只使用普通的 sql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！