Microsoft SQL Server 2005 Inserting into tables from child procedure which returned multiple tables

Question

I've got a child procedure which returns more than table.

child:

PROCEDURE KevinGetTwoTables
AS BEGIN
    SELECT 'ABC' Alpha, '123' Numeric

    SELECT 'BBB' Alpha, '123' Numeric1, '555' Numeric2
END

example:

PROCEDURE KevinTesting
AS BEGIN
    DECLARE @Table1 TABLE (
        Alpha varchar(50),
        Numeric1 int
    )
    DECLARE @Table2 TABLE (
        Alpha varchar(50),
        Numeric1 int,
        Numeric2 int
    )

    --INSERT INTO @Table1
    EXEC KevinGetTwoTables
END

Answer 1

As far as I know, there's no way to capture a second result set inside T-SQL.

You could do it from a CLR stored procedure.

Answer 2

You will not be able to capture both result sets in the parent procedure, like you could with a single one into a temp table. However, there are several other options. Read the excellent article How to Share Data Between Stored Procedures by Erland Sommarskog.

If you are using SQL Server 2008 look at the section on Table Parameters and Table Types. You can pass the tables as output parameters, back to the parent.

If you are not on SQL Server 2008, look at the section on Sharing a Temp Table.

You basically create two #TempTables in the parent procedure, and in the child procedure you populate each of the #TempTables and terminate the child procedure. When you are back in the parent procedure each #TempTable has data in it.