passing parameter values to stored procedure when calling from another procedure

Question

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

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)

Now when I try to call my sp2

exec sp2

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

Answer 1

As simple as,for example:

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