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
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.
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.
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:
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.
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.
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.
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.
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.