从另一个过程调用时将参数值传递给存储过程 [英] passing parameter values to stored procedure when calling from another procedure

问题描述

我有一个存储过程 sp1，它接受参数@id int、@type int、@orderno int

i have a stored procedure sp1 which takes parameters @id int, @type int, @ orderno int

现在，我试图从 sp2 调用 sp1

Now, I am trying to call sp1 from sp2

alter proc sp2
/insert into @temp_tbl exec sp1 1,2,3
set @select = 'select * from @temp_tbl'  --@select is already declared
exec (@select)

现在当我尝试调用我的 sp2

Now when I try to call my sp2

exec sp2

我收到错误:过程或函数sp1"需要参数@id，但未提供.那么，我如何传递参数??

I get the error: procedure or function 'sp1' expects parameter @id, which was not supplied. so, how do I pass the parameters??

推荐答案

就这么简单，例如:

insert into @temp_tbl exec sp1 @id=1, @type=2, @orderno=3

但您显然需要传递值(可能来自其他参数等).

but you obviously need the values to pass (which could come from other parameters, etc).

DECLARE @OrderNoSource INT = 33;
insert into @temp_tbl exec sp1 @id=1, @type=2, @orderno=@OrderNoSource;

所以，一个更完整的例子:

So, a more complete example:

BEGIN TRANSACTION 
GO -- necessary to define the fake sp1 for this script
create procedure sp1 @id int, @type int, @orderno int as SELECT @id, @type, @orderno ;
GO
declare @temp_tbl TABLE (id int, [type] int, orderno int);

insert into @temp_tbl EXEC sp1 1,2,3

SELECT * FROM @temp_tbl; -- this works, no EXEC() needed!

DECLARE @select VARCHAR(MAX); 
SET @select = 'SELECT * FROM @temp_tbl;'
EXEC (@select);  -- FAILS: @temp_tbl is a VARIABLE and NOT IN SCOPE!;

SELECT * INTO  #temp_tbl FROM @temp_tbl ; -- copy local TABLE VAR to TEMP Table (stored in temp db, private)
SET @select = 'SELECT * FROM #temp_tbl;' -- note @ changed to #
EXEC (@select); -- This also works, because temp table are in scope

ROLLBACK -- cleanup this test

这篇关于从另一个过程调用时将参数值传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！