Returning value from sp to another sp

[jsteph]

Hi all,
I'm wondering if it's possible (and how one would go about it) to return a SELECT statement's rowset from a stored procedure to another sp, without a table-value parameter or anything like that.

For example, say I have a simple proc "Proc1":
Select 'x' as x, 'y' as y

...and say I want to use that resultset in "proc2"...say in a cursor (please don't pontificate about the evils of cursors, this is more of an academic question):

So what i'm looking for "Proc2" to look something like this:
Declare cTest cursor for exec Proc1
********* or *********
Declare cTest cursor for Select * FROM exec Proc1

I'm looking for some general guidance here on how this might be accomplished, if it indeed can be accomlished.

I've tried the above cursor statement and it doesn't work, so I'm wondering if it's just a case where it can't be done or if i'm just doing it wrong.
Thanks,
--Jim

 

[RiverGuy]

You can do something like:

DECLARE @Temp1 TABLE (Col1 INT, COl2, VARCHAR(30), --etc.)

INSERT INTO @Temp1 EXEC Proc1

DECLARE cTest CURSOR FOR SELECT * FROM @Temp1

 

[gmmastros]

Normally, you create a temp table (or table variable if you are on SQL2005 or above). Then you use insert/exec to capture the output of the stored procedure. Once the data is in a temp table, you can use that for your cursor.

For example:

Alter Procedure Proc1(@Param1 Int)
AS
If @Param1 = 1
  Select 'x' as x, 'y' as y
Else
  Select 'a' as x, 'b' as y

go

Create Procedure Proc2
as
Create Table #Temp(X Char(1), Y Char(1))

Insert Into #Temp Exec Proc1 1
Insert Into #Temp Exec Proc1 0

Select * From #Temp

Note: I added a parameter to Proc1 so you could also see how that works.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jsteph]

Excellent, riverguy and gmmastros, thanks very much!
--Jim

 

[markros]

Why not return values using OUT parameters?

Correction to George answer.

Table variables were introduced in SQL Server 2000



PluralSight Learning Library

 

[gmmastros]

Table variables were introduced in SQL2000, but you could not insert/exec in to a table variable until SQL 2005.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Check also

http://social.msdn.microsoft.com/Fo...l/thread/30cf328f-7f13-42f1-9ca9-c6c3b08a0d4c

PluralSight Learning Library