使用sp_executesql执行带参数的存储过程

Question

我有一个简单的存储过程，其中有参数。

CREATE Procedure GetSupplierForTesting
    (@SupplierId INT)
AS
    SELECT SuppLabel 
    FROM Supplier 
    WHERE Supplier.SupplierId = @SupplierId

我可以在另一个存储过程中使用exec命令来调用它，就像这样：

exec GetSupplierForTesting @SupplierId = 10

我看到一篇文章解释了如何使用 sp_executesql 比使用 exec 更快。我的问题是，我不知道如何使用 sp_executesql 调用具有参数的存储过程。我已经尝试了以下代码：

DECLARE @SupplierId INT = 10;
EXEC sp_executesql N'GetSupplierForTesting', N'@SupplierId INT', @SupplierId

但是我遇到了一个错误：

'GetSupplierForTesting' 这个过程或函数需要参数 '@SupplierId'，但是未提供该参数。

- AnotherGeek

---

如果可能的话，始终使用EXEC spName运行存储过程，性能注释仅与EXEC <SQL string here>使用的EXEC相关 - 例如，如果您将SQL语句构建为字符串或者在变量中有一个过程名称。 - Alex K.

还要注意，在调用那个存储过程之前，您仍然需要使用'EXEC'。 - Martin Smith

你能否在问题中添加文章链接？在任何情况下，sp_executesql都不可能是执行存储过程更快的方法，这听起来非常不可信。 - James Z

@JamesZ 参考 https://msdn.microsoft.com/zh-cn/library/ms175170.aspx，但正如Martin Smith所解释的那样，只有在将SQL语句构建为字符串时才更快。 - AnotherGeek

是的，这篇文章只是指查询计划的重复使用 - 而且这仅适用于动态SQL。 - James Z

Answer 1

您所需要的语法应该是：

你需要的语法是

DECLARE @SupplierId INT = 10;

EXEC sys.sp_executesql N'GetSupplierForTesting @SupplierId=@SupplierId', 
                       N'@SupplierId INT', 
                         @SupplierId=@SupplierId

但请不要这样做。这完全没有意义。使用sp_executesql基本上只是将相同的exec语句包装起来，并在不同的范围内执行它，并不会带来任何神奇的性能提升。

只需使用

exec dbo.GetSupplierForTesting @SupplierId = 10

- Martin Smith

---

遗憾的是，对于这种“错误”，没有错误/警告信息 @SupplierId=@SupplierId。 - Jaider

Answer 2

1

性能问题是基于这样的假设：当您使用非参数化的即席查询时，表示此查询的字符串将在每次使用时都不同，因为特定的参数值是查询文本的一部分。

而参数化查询则保持其主体不变，因为您有where … and title = @title代替了where … and title="asdf"。仅变量@title的内容会更改。但是查询文本仍然存在，SQL Server会意识到没有必要重新编译它。

每次更改其中使用的值时，非参数化查询都将被重新编译。

2

您遇到异常是因为您的脚本没有向存储过程传递任何参数。

您的脚本只是：'GetSupplierForTesting' - 就是这样。

通过向sp_executesql传递参数，您将它们传递给脚本而不是用于脚本的存储过程。例如：

exec sp_executesql N'print @val', N'@val int', @val = 1

这个脚本确实使用了变量@val。而你的脚本没有使用，它只包含存储过程的名称。因此，你的脚本应该更正为：

exec sp_executesql
    N'exec GetSupplierForTesting @Supplier = @Supplier_id_value', 
    N'@Supplier_id_value int',
    @Supplier_id_value = 10

1. 脚本包含调用您的sp的代码，并且此代码使用从@Supplier_id_value变量中取得的值作为参数传递给sp。
2. @Supplier_id_value在此脚本内部被声明为int。
3. 将值10传递给脚本的参数。

3

您所谈论的性能问题是关于 ad-hoc，而不是 SPs。

这个问题的另一个方面是参数嗅探问题。有时，针对特定的参数值，您的脚本或SP应该使用与之前传递的参数值不同的执行计划。

每次重新编译（“慢执行”）ad-hoc都会被重新编译并可能获得更好的执行计划，而SP或参数化查询可能不会重新编译，并且将使用更差、不太优化的执行计划，最终执行速度比“因重新编译而变慢”的ad-hoc查询要慢得多。

在sql中没有“写这个-它会慢”，“写那个-它会像火箭一样飞行”的规则。一切都取决于许多因素。有时可能需要专门的ad-hocs，有时应完全避免它们。

- Ivan Starostin