Hi all i'm looking for a way to pass in a generated id from a sproc to another sproc.
大家好我正在寻找一种方法将生成的id从sproc传递到另一个sproc。
i.e.
@name varchar(12),
@descript varchar(55)
@test_date date,
@test_result varchar(1)
BEGIN
EXEC ts_create_item @name, @descript
END
if (@test_date is not null) or (isnull(@test_result_id,'')!='')
BEGIN
Exec ts_update_test_results @itemid(generated from the sproc before), @test_date, @test_result
End
Is there a way to do this in sql server?
有没有办法在sql server中执行此操作?
Thanks
1 个解决方案
#1
Use an output variable
使用输出变量
so you would declare the previous proc like
所以你要声明前面的过程就像
Create proc SomeProc (@parm1 int, @parm2 int, @id int = null OUTPUT)
as
Begin
...do some insert
select @id = scope_identity()
End
And remember, OUTPUT has to be specified both when declaring and assigning the parameter ie.
请记住,在声明和分配参数时,必须指定OUTPUT。
Exec someproc @parm1, @parm2, @id OUTPUT
Alternately you can use a local variable to hold the result
或者,您可以使用局部变量来保存结果
e.g.
create proc somesample(@in int)
as
Begin
select @in * 2
End
declare @var int
exec @var = somesample 1
print @var
2
#1
Use an output variable
使用输出变量
so you would declare the previous proc like
所以你要声明前面的过程就像
Create proc SomeProc (@parm1 int, @parm2 int, @id int = null OUTPUT)
as
Begin
...do some insert
select @id = scope_identity()
End
And remember, OUTPUT has to be specified both when declaring and assigning the parameter ie.
请记住,在声明和分配参数时,必须指定OUTPUT。
Exec someproc @parm1, @parm2, @id OUTPUT
Alternately you can use a local variable to hold the result
或者,您可以使用局部变量来保存结果
e.g.
create proc somesample(@in int)
as
Begin
select @in * 2
End
declare @var int
exec @var = somesample 1
print @var
2