SQL Server 2012 - WITH RESULT SETS

What is WITH RESULT SETS  in SQL Server 2012 ?

                            WITH RESULT SETS  provides flexibility in getting output of stored procedure in two ways :

                              1.) Column names returned as the result of stored procedure execution can be changed
                              2.) Data type of columns returned as the result of stored procedure execution can be changed


                             WITH RESULT SETS can be used only at the time of executing stored procedure.

Examples for understanding  WITH RESULT SETS :

USE AdventureWorks2012

--Example 1 

CREATE PROCEDURE With_Resultset
--Procedure to get top 5 married employee details
AS
BEGIN

        SELECT TOP 5 
               BusinessEntityID,
               NationalIDNumber,
               JobTitle,
               Gender,
               HireDate 
        FROM [HumanResources].[Employee] WHERE MaritalStatus = 'M'

END

--  actual column names returned by stored procedure are changed using WITH RESULT SETS

EXECUTE With_Resultset
WITH RESULT SETS
  (
      (
               BusinessID    INT,
               NationalID    NVARCHAR(20),
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
      ) 
  ) 
GO

--Example 2 - Only compatible data type conversion is possible

-- In below example - column NationalID data type NVARCHAR(20) is converted to INT

EXECUTE With_Resultset
WITH RESULT SETS
  (
      (
               BusinessID    INT,
               NationalID    INT,
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
      ) 
  )
GO

   

--Incompatible data type conversion is not possible

--In below example - column Sex data type NCHAR(10) is converted to INT

EXECUTE With_Resultset
WITH RESULT SETS
  (
      (
               BusinessID    INT,
               NationalID    INT,
               Designation   NVARCHAR(100),
               Sex           INT,
               DateOfJoining DATE
      ) 
  )
GO

Msg 8114, Level 16, State 2, Procedure With_Resultset, Line 8
Error converting data type nchar(1) to int.


-- Example - 3 : Cannot exclude columns returned by stored procedure while defining WITH RESULT SETS
--In the below example DateOfJoining column is omitted while defining  WITH RESULT SETS

--Note : also cannot include extra columns while defining WITH RESULT SETS

EXECUTE With_Resultset
WITH RESULT SETS
  (
      (
               BusinessID    INT,
               NationalID    INT,
               Designation   NVARCHAR(100),
               Sex           NCHAR
      )
  )
GO

Msg 11537, Level 16, State 1, Procedure With_Resultset, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 4 column(s) for result set number 1, but the statement sent 5 column(s) at run time.

--Example - 4:
-- WITH RESULT SETS NONE - If any results are returned by stored procedure ,then batch will be aborted.

EXECUTE With_Resultset
WITH RESULT SETS NONE

 
Msg 11535, Level 16, State 1, Procedure With_Resultset, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.

--Example - 5 :
--WITH RESULT SETS UNDEFINED -  stored procedure will execute without error irrespective of any results are returned or no results are returned by stored procedure.
 

EXECUTE With_Resultset
WITH RESULT SETS UNDEFINED 

 

--Example - 6:

CREATE PROCEDURE With_Multiple_Resultset
--Procedure to get top 5 single & married employee details
AS
BEGIN

        SELECT TOP 5
               BusinessEntityID,
               NationalIDNumber,
               JobTitle,
               Gender,
               HireDate
        FROM [HumanResources].[Employee] WHERE MaritalStatus = 'M'

        SELECT TOP 5
               BusinessEntityID,
               NationalIDNumber,
               JobTitle,
               Gender,
               HireDate
        FROM [HumanResources].[Employee] WHERE MaritalStatus = 'S'

END

--Example for multiple result sets
EXECUTE With_Multiple_Resultset
WITH RESULT SETS
  (
      (
               BusinessID    INT,
               NationalID    NVARCHAR(20),
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
      ),
      (
               BusinessID    INT,
               NationalID    NVARCHAR(20),
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
      )
  )
GO

--Example 7 : -- Inserting into temp table output of stored procedure executed using WITH RESULT SETS is not possible
--without (WITH RESULT SETS)

CREATE TABLE #TMP1
(
               BusinessID    INT,
               NationalID    NVARCHAR(20),
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
)
INSERT INTO #TMP1
EXECUTE With_Resultset
GO
SELECT * FROM #TMP1

--with (WITH RESULT SETS)

CREATE TABLE #TMP2
(
               BusinessID    INT,
               NationalID    NVARCHAR(20),
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
)
INSERT INTO #TMP2
EXECUTE With_Resultset
WITH RESULT SETS
  (
      (
               BusinessID    INT,
               NationalID    NVARCHAR(20),
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
      )
  )
GO
SELECT * FROM #TMP2

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'SETS'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#TMP2'.


Restrictions in using  WITH RESULT SETS

                               Number of columns defined inside WITH RESULT SETS should always match the number of columns returned as the result of stored procedure execution

                                Data type of columns  returned as the result of stored procedure execution can be changed,but incompatible data type conversion is not possible.

                               Below method is not possible using WITH RESULT SETS

CREATE TABLE #TMP ...
INSERT INTO #TMP
EXECUTE Procedure
WITH RESULT SETS
  (
   )

SELECT * FROM #TMP                                

See Also:

• My posts on T-SQL