It is not uncommon to have stored procedures returning result sets, and although it sounds like a good idea, it becomes a problem when you want to have control of the name of the columns and data type of the columns being returned as part of the result set. SQL Server 2012 (Code name SQL Denali) introduces a new feature WITH RESULT SETS which lets you redefine the name and data types of the columns being returned from the stored procedure. This article talks about this new feature in detail.
Understanding WITH RESULT SETS clause
SQL Server 2012 (Code name SQL Denali) introduces the WITH RESULT SETS clause with the EXECUTE statement, which lets you redefine the name and data types of the columns being returned from the stored procedure. This comes in very handy when you want to insert the records coming in a result set to a temporary table with a specific column name and data type and you don’t want to rely on what is being returned by the stored procedure.
The WITH RESULT SETS clause can also be used with a stored procedure, which returns multiple result sets and for each result set you can define the column name and data types for each column separately.
- Sometimes if you want to restrict a stored procedure to return a result set you can use the RESULT SETS NONE clause.
- The WITH RESULT SETS option cannot be specified in an INSERT…EXEC statement.
- The number of columns being returned as part of result set cannot be changed.
Enough of theory now, let’s do some hands on around this new feature.
Example – Usage
In the example below, I am creating a table and inserting some data to it; then I am creating a stored procedure, which selects data from the table and returns the result set. Next if you notice, the “EXECUTE GetEmployees call” returns the result set in the format (column name and data type) in the format specified in the body of the stored procedure and you don’t have any control to change it. Finally you can see the EXECUTE GetEmployees call with WITH RESULT SETS clause, which gives you a control on renaming the columns and defining a different data type (though compatible only) of each column of the result set being returned:
Figure 1 – WITH RESULT SETS example with single result set
As I said before, not only you can use the WITH RESULT SETS clause with a stored procedure that returns a single result set, as shown above, but you can also use it with a stored procedure that returns multiple result sets, as shown below. In the example below you can see the stored procedure returns two result sets and the column name and data type of each column from each result set is being redefined separately.
Figure 2 – WITH RESULT SETS example with multiple result sets
In this article I talked about a new feature (WITH RESULT SETS), which can be used along with the EXECUTE statement to redefine the name and data types of the columns being returned as a result set from the stored procedure. This new feature can be used with a stored procedure that returns multiple result sets as well and each of these result sets can be redefined/formatted separately.