WITH RESULT SETS allows column names and their data types to be changed in the result set of a stored procedure.
My first thought with this improvement is that it could be very useful to have when building SSIS packages which read from a stored procedure as a data source. We’ll take a look at that in a future post but in addition to that, it helps to replace some of the following…
Before Server 2012 WITH RESULT SETS you had to…
If you wanted to change the column names and their data types after executing a stored procedure, then in previous versions there were a few things to try.
- Create a copy of the stored procedure and change it to return different column names or data types.
- Execute the stored procedure and insert the results into a temporary table, then read from the temporary table.
- Edit the same stored procedure and change its output.
- Create a UDF and select from that.
Oh dear, not good.
Some of the issues which spring to mind with those points are code duplication, sub-optimal performance because of the use of temp tables and just hassle in writing lengthy code. I’m going to try and look at some of these in a future post.
So lets check WITH RESULT SETS syntax
To get it working you supply WITH RESULT SETS and parentheses followed by the column names and data types as follows:
EXEC Your_Stored_Procedure WITH RESULT SETS ( ( Col1_Renamed INT, Col2_Renamed VARCHAR(50) ) )
If your stored procedure is returning multiple result sets then it is possible to manipulate all of them. For example:
EXEC Your_Other_Stored_Procedure WITH RESULT SETS ( ( Col1_Renamed INT, Col2_Renamed VARCHAR(50)) , ( Col3_Renamed TINYINT, Col4_Renamed NVARCHAR(100) ) )
Please note that the number of columns being returned in the result set cannot be changed.
Lets look at a demo of it in action against the AdventureWorks2012 database. First, I will create a new stored procedure…
CREATE PROCEDURE GetCustomerOrders @CustomerID INT AS SELECT SalesOrderID , OrderDate FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID
Now, I’m going to execute it and use the WITH RESULT SETS to change the output.
EXEC GetCustomerOrders @CustomerID = 11000 WITH RESULT SETS ( ( OrderID INT, DateOfOrder VARCHAR(20) ) )
OrderID DateOfOrder ----------- -------------------- 43793 Jul 22 2005 12:00AM 51522 Jul 22 2007 12:00AM 57418 Nov 4 2007 12:00AM (3 row(s) affected)
Without the WITH RESULT SETS it looks like this
SalesOrderID OrderDate ------------ ----------------------- 43793 2005-07-22 00:00:00.000 51522 2007-07-22 00:00:00.000 57418 2007-11-04 00:00:00.000 (3 row(s) affected)
WITH RESULT SETS conclusion
Another nice addition to the T-SQL features in SQL Server 2012 which helps to streamline code when formatting result sets produced by stored procedures.
Anything which makes life more simple is always welcome
If you want more information click this link on SQL Server 2012 WITH RESULT SETS