Get return values from a stored proc inside a stored proc

18 Nov 2005 6:37 AM

Calvin KD

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

18 Nov 2005 10:50 AM

MDFS

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

Show quote

"Calvin KD" wrote:

> 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
>
>

20 Nov 2005 11:40 PM

Calvin KD

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

Show quote

"MDFS" wrote:

> 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
>
>
>
> "Calvin KD" wrote:
>
> > 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
> >
> >