Calling a Stored Proc from within a Stored Proc and returning a recordset.

Question

I have a Stored Procedure that rolls-back a series of operations. I want to call this from within another SP.

The problem is that the inner SP returns a record set with a single value that indicates the degree of success.

This approach worked well and has some advantages in our context, but in retrospect, I would have done it the conventional way with a Return value or an Output parameter.

I could always change this SP to use this approach and modify the calling code, but a) I don't want to dabble with any more code than I have to, and b) at an intellectual level, I'm curious to see what alternative solution there may be, if any.

How (if at all) can I call this SP and determine the value of the singleton recordset returned?

Thanks

Answer 1

A stored procedure returns a record set like any other, so you can actually do this:

INSERT INTO MyTable ( MyValue )

EXEC dbo.MyStoredProcedure

The EXEC takes the place of a SELECT statement. To get the value, just SELECT from the table you inserted into. Typically, this would be a temp table.

Answer 2

The other option is to convert the stored procedure that returns a recordset into a function that returns a table.

Ant's approach is probably best if you want to minimize the changes to your system.

Normally you would use a temporary table for that approach since you can't use an exec statement to insert into a table variable.

Answer 3

Here's a variation which will work well if you need to use this for MULTIPLE recordsets.

CREATE TABLE #outsidetable (...)
exec spInsideProcedure
SELECT * FROM #outsidetable

inside spInsideProcedure

INSERT INTO #outsidetable SELECT <blah blah blah>

Answer 4

I tried Ant's approach and it worked a treat:

Declare @Success tinyint
Declare @Response Table (Success int)
Insert into @Response(Success)
Exec Fix_RollbackReturn 12345, 15
Select @Success=Success from @Response

As you can see I used a Table Variable rather than a temporary table because slightly more efficient than a temporary table.

Thanks for all your help guys.

EDIT: It appears that Dave was right after all. That is, my Exec-into-Table-variable approach worked on my SQL2005 development machine, but when moved to the Live (SQL2000) machine it objected, so I had to change to the temporary table approach.

It's a little annoying, especially since in a couple of weeks we are upgrading to SQL2005 across the board(!).