With the introduction of SQL Server 2012, a useful feature was included: EXECUTE WITH RESULT SETS. This feature allows us to modify the column names and column data types of the result sets returned by a stored procedure without actually modifying the stored procedure code.

In a real world scenario, we have a legacy stored procedure which shows a result set with one column “CustomerName” having varchar(50) as output, and it is being called from the .NET front end application to execute. Due to a sudden requirement change, front end application developers have modified the code and expect the column name alias as “CustomerFullName” and datatype length as varchar(100). To fix this, we need to modify the existing stored procedure code to adhere to these new changes, which could impact some other stored procedures that have a dependency on it.

To make a quick fix and consider the requirement changes, we can use SQL Server’s new feature EXECUTE WITH RESULT SETS by changing the column name alias and data type while executing the stored procedures. This gives us the benefit of not modifying the existing stored procedure code.

Uses of Execute WITH RESULT SETS in SQL Server

1. To change the column name and column data type at runtime while executing a stored procedure

2. Can be used with multiple result sets returned by queries to change the column names and data types at runtime

Limitations of Execute WITH RESULT SETS in SQL Server

1. We cannot remove any column from an existing result set. If the result set is giving three columns as output, we need to define all three columns using the WITH RESULT SETS option.

2. We cannot change the order of the columns in the result set.

3. WITH RESULT SETS cannot be nested with INSERT… EXEC call to store the results into a temporary table.

Syntax: WITH RESULTS SET

EXECUTE <Stored_Procedure_Name>  <Parameter [Optional]>
 WITH RESULT SETS
   { RESULT SETS UNDEFINED } | 
   { RESULT SETS NONE } | 
   { RESULT SETS ( <Colmn_Name> <Data_Type>) } 

1. EXECUTE WITH RESULT SETS with RESULT SETS UNDEFINED

RESULT SETS UNDEFINED is a default option with EXECUTE WITH RESULT SETS. If no option is specified using WITH RESULT SETS, this is considered by default. This executes without raising any error returning the result sets, if any.

2. EXECUTE WITH RESULT SETS with RESULT SETS NONE

RESULT SETS NONE indicates no result sets will be returned by the executed statement. If this option is used and the statement returns any result set, it raises an error.

Examples: EXECUTE WITH RESULT SETS

We can drive through a few examples, creating a table “Player” and inserting few records.

IF OBJECT_ID(N'Player',N'U') IS NOT NULL
DROP TABLE Player;
GO

Create table Player
(
Id            INT,
Name          VARCHAR(100),
HighestScore  INT
);

INSERT INTO Player VALUES
(1, 'Niraj' , 120),
(2, 'Vish'  , 51),
(3, 'Chetan', 264);

1. EXECUTE WITH RESULT SETS: Modify Column Name/Data Type

We can create a stored procedure to return all player names and their highest score.

CREATE PROCEDURE GetPlayerNameAndScore
AS
BEGIN
    SELECT Name, HighestScore
    FROM  Player;
END

If we execute the procedure in the usual way, EXEC GetPlayerNameAndScore, we will get the Name and HighestScore column in the result set as follows:

EXEC GetPlayerNameAndScore

Now considering we want to return the result set with a new column alias without modifying the existing stored procedure, return the result sets with PlayerName with VARCHAR(50) data type and PlayerHighScore column instead of their original names. We can use EXECUTE WITH RESULT SETS.

EXEC GetPlayerNameAndScore
WITH RESULT SETS 
(
  ( 
	PlayerName        VARCHAR(50),
	PlayerHigherScore INT
  )
);

Below, you can see the column alias has been modified to adhere to the new change.

2. EXECUTE WITH RESULT SETS: Multiple Result Sets

We can create a stored procedure to return multiple result sets: Number of records in Player table & List of players whose name starts with C.

CREATE PROCEDURE Get_Player_Name_Score_Multiple_ResultSets
AS
BEGIN
        SELECT Name
        FROM  Player
        WHERE Name Like 'C%';

        SELECT COUNT(1) as MyCount 
        FROM   Player;

END

We can use EXECUTE WITH RESULT SETS to change the alias of the first result from Name to PlayerName and in the second result set, to change the MyCount alias to PlayerCount.

EXEC Get_Player_Name_Score_Multiple_ResultSets
WITH RESULT SETS 
(
    ( 
         PlayerName   VARCHAR(50)  -- First Result Set
    ),
    ( 
         PlayerCount   INT	   -- Second Result Set
    )
);

3. EXECUTE WITH RESULT SETS: Columns Mismatch Error

We are using the procedure created in Example 1, GetPlayerNameAndScore, which is returning two columns in a result set, and if we only define one column with EXECUTE WITH RESULT SETS, SQL server raises the following error. Thus it is necessary to include all the columns with the EXECUTE WITH RESULT SETS option.

Trying to include only the first column in a result set is not allowed.

EXEC GetPlayerNameAndScore
WITH RESULT SETS 
(
    ( 
            PlayerName   VARCHAR(50)             
    )
);

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