Saturday 30 November 2013

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.