sp_executesql执行sql变量的问题

Question

存储过程里需要查询并记录一条数据脚本如下

declare @totalSql nvarchar(max)
declare @sum_amount decimal
declare @sum_count decimal


set @totalSql='
select @sum_amount=isnull(sum(t2.detail_amount),0),@sum_count=isnull(sum(t2.number),0)
from merchant_purchase_order t1
inner join merchant_purchase_order_detail t2 on t2.order_id=t1.id
inner join shopping_goods t3 on t3.id=t2.goods_id and t3.goods_kind=1
where order_state between 1 and 10 '

exec sp_executesql  @totalSql  

最后执行抛出异常：必须声明标量变量 "@sum_amount"。

请教下是有其他方法，还是这个方法的写法不对

Answer 1

根据您提供的存储过程脚本，可以看到您使用了动态 SQL，将 SQL 语句存储在字符串变量中并使用 sp_executesql 函数执行。在这种情况下，需要特别注意变量的作用域，因为动态 SQL 在执行时是在独立的作用域中运行的，它无法直接访问当前作用域中声明的变量。

针对您遇到的错误 "必须声明标量变量 '@sum_amount'"，这是因为动态 SQL 执行时无法识别当前作用域中的变量 @sum_amount。为了解决这个问题，您可以使用 OUTPUT 参数来从动态 SQL 中返回结果，例如：

DECLARE @totalSql nvarchar(max)
DECLARE @sum_amount decimal
DECLARE @sum_count decimal
 
SET @totalSql='
SELECT @sum_amount = ISNULL(SUM(t2.detail_amount),0), @sum_count = ISNULL(SUM(t2.number),0)
FROM merchant_purchase_order t1
INNER JOIN merchant_purchase_order_detail t2 ON t2.order_id=t1.id
INNER JOIN shopping_goods t3 ON t3.id=t2.goods_id AND t3.goods_kind=1
WHERE order_state BETWEEN 1 AND 10'
 
EXEC sp_executesql @totalSql, N'@sum_amount DECIMAL(18, 2) OUTPUT, @sum_count DECIMAL(18, 2) OUTPUT', @sum_amount OUTPUT, @sum_count OUTPUT

在上面的示例中，我们将 @sum_amount 和 @sum_count 声明为 OUTPUT 参数，并将它们传递给 sp_executesql 函数。在动态 SQL 中，我们使用 SELECT 语句并将结果分配给 OUTPUT 参数，这样就可以在当前作用域中访问它们。