Accessing ResultSet in Stored Procedure

Question

Post reply

Accessing ResultSet in Stored Procedure

Karl Coleman

Valued Member

Points: 58
More actions
November 29, 2004 at 3:07 pm

#102295

How do I access the resultset of an execution within a stored procedure? I want to either execute another stored procedure or run a SELECT statement and process the results in the stored procedure I am currently running? Is there a way to access the resultset or assign a cursor to the execution results?
Thanks,
Karl

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 1

You can use a

INSERT INTO #mytemptable

EXEC myProcedure

type of statement to collect the result set into a temporary table.

Don't know if a cursor can do it. I don't use them

Julian Kuiters
juliankuiters.id.au

Shane Kelly Valued Member Points: 61 More actions · Answer 2

There are a few different ways to do this. The method I use the most is to create a temp table in the parent stored procedure and then insert the record set returned by the child proc.

Create Proc ParentProc

As

Create Table #tmp

(

LastName Varchar(50),

FirstName VarChar(50),

)

Insert Into #tmp (LastName, FirstName)

Exec ChildProc

go

Create ChildProc

As

Select LastName,

FirstName

From Employee

go

In order to use this method you must know the data structure of the result set returned by the child stored proc ahead of time, so that you can create the tmp table to hold it.

Another method is to stuff the result set into a table in the child proc.

The third method is to return a cursor from the child proc. But since you should always try and use batch operations before settling on a cursor, this should be a last resort.

Karl Coleman Valued Member Points: 58 More actions · Answer 3

Thanks. I have only been working with stored procedures for a few months, and most have been simple, so forgive me if I am asking inane questions.

Ok. So I now have this #tmp table. Now what? I know how to use cursors such as

declare mycursor cursor for

SELECT * FROM #tmp

open mycursor

But you say do that as a last resort. How else would I access what is in this temporary table? Or did you mean only return cursors as a last resort, not applying to use of cursors in general?

Thanks,

Karl