sp_executesql执行sql变量的问题

永动bug制造机 2023-02-20 09:33:29

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

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"。

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

...全文

38 2 打赏收藏举报

写回复

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

雨夹雪 02-20

打赏
举报



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 output,@sum_count decimal output',@sum_amount OUTPUT,@sum_count OUTPUT