Get return values from a stored proc inside a stored proc

[Guest]

Hi everyone,
Can someone show me how to obtain values returned from a called stored proc
inside a calling stored proc. For example:
StoredProc1
========
CREATE PROCEDURE StoredProc1(@keyID Int) AS
SELECT value1, value2, value3
FROM Table1
WHERE id = @keyID
GO

StoredProc2
========
CREATE PROCEDURE StoredProc2() AS
....
EXEC StoredProc1 @keyID = 1

I need to obtain value1, value2 and value3 from the StoredProc1.

Any suggestion is greatly appreciated.
Calvin

 

[Guest]

Calvin there are at least 2 ways of doing this
1) Using your existing setup
==================
CREATE PROCEDURE StoredProc1(@keyID Int,@Value1 int output,@Value2 int
output,@Value 3 int output) AS
SELECT @Value1=value1, @value2=value2, @Value3=value3
FROM Table1
WHERE id = @keyID
GO

StoredProc2
========
CREATE PROCEDURE StoredProc2() AS
...
EXEC StoredProc1 1,@Value1 output,@Value2 output,@Value3 output
*************************************************

OR you can convert stored procedure 1 to a function and use it like this:


CREATE Function StoredProc1(@keyID Int) AS
returns table
return SELECT @Value1=value1, @value2=value2, @Value3=value3
FROM Table1
WHERE id = @keyID
GO

StoredProc2
========
CREATE PROCEDURE StoredProc2() AS
...
Select * from StoredProc2(@KeyID)
Select @Value1=Value1,@Value2=Value2,@Value3=Value3 from StoredProc2(@KeyID)



Hope this helps
Myles

 

[Guest]

Thanks so much Myles. Really appreciated your response. It certainly helps.
Thanks.
Calvin