设置@var = execstored_procedure [英] set @var = exec stored_procedure

问题描述

是否可以在变量中分配从exec存储过程返回的值?

Is it possible to assign at variable a value returned from exec stored procedure?

类似

DECLARE @count int
SET @count = Execute dbo.usp_GetCount @Id=123

推荐答案

您可以使用sp_executesql代替exec来分配标量输出参数

You can use sp_executesql instead of exec to assign to scalar output parameters

DECLARE @out int

EXEC sp_executesql N'select @out_param=10',
                   N'@out_param int OUTPUT',
                     @out_param=@out OUTPUT

SELECT @out

对于exec，我只知道如何使用表变量

For exec I'm only aware of how to do it using a table variable

declare @out table
(
out int
)

insert into @out
exec('select 10')

select * 
from @out

对于存储过程，您还可以使用output参数或返回码.后者只能返回一个整数，通常首选返回错误代码而不是数据.这两种技术都在下面进行了演示.

For stored procedures you would also use an output parameter or a return code. The latter can return a single integer only and generally preferred for returning error codes rather than data. Both techniques are demonstrated below.

create proc #foo 
@out int output
as
set @out = 100
return 99

go

declare @out int, @return int

exec @return = #foo @out output

select @return as [@return], @out as [@out]

drop proc #foo

这篇关于设置@var = execstored_procedure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！