2

I have a stored procedure SP1 which is returning two tables T1 and T2 using SELECT Statements. I want to perform some operation on T2 in another Stored Procedure SP2. Can someone guide me how I can do that?

Usman Khalid
  • 3,032
  • 9
  • 41
  • 66
  • I don't think you'll be able to do that with SP, what you need is a table valued function. – yogi Aug 22 '12 at 07:13
  • 2
    There is no way of accessing just the second result set from a stored procedure in TSQL. If both result sets are union compatible then you can `INSERT INTO @T EXEC YourProc` but this will insert **both** result sets. – Martin Smith Aug 22 '12 at 08:43

1 Answers1

2

Option 1

Change SP1 to save T2 to a table, in addition to returning it using a SELECT. Then use that table as input for SP2.

Option 2

If you can't change SP1, you can catch the output of the stored procedure in a table. Check out this question on StackOverflow on how to do that by using OPENROWSET or by defining a temp table in advance: How to SELECT * INTO [temp table] FROM [stored procedure].

This option will not work if T1 and T2 do not have the same definition. If they have the same definition, you can import both result sets into one table as follows:

--create a temporary table with the same definition as T1 and T2
CREATE TABLE #temp (
    Col1 SOME DATATYPE,
    Col2 SOME DATATYPE
);

--fill your temp table with the result sets of T1 and T2
INSERT INTO #temp
EXEC [dbo].[SP1];

But then you have to deal with the T1-plus-T2 table instead of T2.

So it seems option 1 is your best bet.

Community
  • 1
  • 1
Josien
  • 13,079
  • 5
  • 36
  • 53
  • 1
    Actually I was trying to achieve it using something like option 2. But thanks for telling the option 1. Thanks a lot. – Usman Khalid Aug 22 '12 at 09:42
  • You're welcome! I can imagine you wanted to go the option 2 route, must say I was slightly surprised that there seems to be no obvious way to handle multiple result sets from a SP in SQL Server. – Josien Aug 22 '12 at 09:53