• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

New T-SQL features in SQL Server 2012 – WITH RESULT SETS

April 21, 2012 by Andy Hayes Leave a Comment

sql server 2012 with result setsNew in SQL Server 2012 comes an extension to EXECUTE in the form of WITH RESULT SETS.

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.

  1. Create a copy of the stored procedure and change it to return different column names or data types.
  2. Execute the stored procedure and insert the results into a temporary table, then read from the temporary table.
  3. Edit the same stored procedure and change its output.
  4. 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:
[sourcecode language=’sql’]EXEC Your_Stored_Procedure
WITH RESULT SETS
(
(Col1_Renamed INT,
Col2_Renamed VARCHAR(50))
)[/sourcecode]
If your stored procedure is returning multiple result sets then it is possible to manipulate all of them. For example:
[sourcecode language=’sql’]EXEC Your_Other_Stored_Procedure
WITH RESULT SETS
(
(Col1_Renamed INT,
Col2_Renamed VARCHAR(50)),
(Col3_Renamed TINYINT,
Col4_Renamed NVARCHAR(100))
)[/sourcecode]
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…
[sourcecode language=’sql’]CREATE PROCEDURE GetCustomerOrders @CustomerID INT
AS

SELECT
SalesOrderID
, OrderDate
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID[/sourcecode]
Now, I’m going to execute it and use the WITH RESULT SETS to change the output.
[sourcecode language=’sql’]EXEC GetCustomerOrders @CustomerID = 11000
WITH RESULT SETS
(
(OrderID INT,
DateOfOrder VARCHAR(20))
)[/sourcecode]
Which returns…

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

Related Posts:

  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…
  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…

Filed Under: All Articles, SQL Tips and Tricks Tagged With: sql server 2012, t-sql

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©