Using Insert Exec with multiple result sets

Sometimes, there is a need to get result sets from a stored procedure into a table. One deceptively simple method is with a Insert..Exec statement.

This works fine for a simple example such as:

USE tempdb
GO

CREATE PROC dbo.tempproc AS
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt

GO

DECLARE @table TABLE
    (
      FirstChar CHAR(1) ,
      SecondChar CHAR(2) ,
      FirstInt INT
    )
INSERT  @table
        EXEC dbo.tempproc
SELECT  * FROM    @table


This returns our table, as we might expect.

However, the situation gets a little more complex if we return from our stored procedure two datasets, which have the same metadata:

ALTER PROC dbo.tempproc AS
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt

    SELECT  'c' AS FirstChar ,
            'd' AS SecondChar ,
            2 AS FirstInt
GO

Now, when we run our code to capture the dataset we get:

This is perhaps a little surprising - and gives us the scenario where the results from our stored proc are indistinguishable from what we would get if we had:

ALTER PROC dbo.tempproc AS
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
           UNION ALL
    SELECT  'c' AS FirstChar ,
            'd' AS SecondChar ,
            2 AS FirstInt
GO

However, if we accessed this same data via a different method (e.g. from CLR or an application looking at distinct result sets) these would be distinguishable. This is in my opinion counter intuitive behaviour which can give us a misleading result.

Suppose you were capturing a result which would be either in the first result set or in the second (the other to be returned empty). It follows that you couldn't use Insert..Exec to capture this as you would be unable to determine which set had caused it. In other words, the results returned using Insert Exec with the following two queries are indistinguishable:

ALTER PROC dbo.tempproc AS
    SELECT TOP 0 'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
          
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
GO

ALTER PROC dbo.tempproc AS
    SELECT  'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
          
    SELECT  TOP 0 'a' AS FirstChar ,
            'b' AS SecondChar ,
            1 AS FirstInt
GO

Try running the queries individually and you will see what I mean!

This is something to be aware of, both when coding and testing stored procedures, especially if Insert..Exec is used to capture information.