Overview of WITH RESULT SETS Feature of SQL Server 2012

By:   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | 5 | > TSQL


While looking through the new features and improvements in SQL Server 2012, we found a potentially interesting feature WITH RESULT SETS. This is a very useful feature especially when one needs to display a result set of a stored procedure by changing the names and data types of the returning result set. In this tip we will take a look at an example which uses the new WITH RESULT SETS feature in SQL Server 2012.


There was always a limitation in the previous versions of SQL Server that whenever you wanted to change a Column Name or a Data Type within the result set of a Stored Procedure you ended up making changes to all the references within a Stored Procedure. This limitation has been overcome with the release of WITH RESULT SETS feature in SQL Server 2012.

Let's go through an example which demonstrates using WITH RESULT SETS syntax to change the names and data types of the returning result set.

USE tempdb
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
DROP TABLE [dbo].[Person]
CREATE TABLE [dbo].[Person]
 [ID]   [INT]   NOT NULL,
 [PersonType]  [NCHAR](15)  NOT NULL,
 [FirstName]  NVARCHAR(50) NOT NULL,
 [MiddleName]  NVARCHAR(50)  NULL,
 [LastName]  NVARCHAR(50)  NOT NULL,
INSERT INTO dbo.Person VALUES (1,'Employee','Shannon','L','Johnston')
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsingWithResultSetsFeatureOfDenali]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UsingWithResultSetsFeatureOfDenali]
CREATE PROCEDURE UsingWithResultSetsFeatureOfDenali
  FirstName + ' ' + MiddleName +' '+ LastName AS Name, 
 FROM dbo.Person
/* Execute Stored Procedure */
EXEC UsingWithResultSetsFeatureOfDenali
/* Using WITH Result Sets Feature Of Denali (SQL Server 2011) */
EXEC UsingWithResultSetsFeatureOfDenali 
  ContactPersonName VARCHAR(150),
  PersonType VARCHAR(15)

In the above example, you can see that using WITH RESULTS SETS feature of SQL Server 2012 we have changed the Column Name and Data Type to meet our needs irrespective of the Column Name and Data Type returned within the result set of the Stored Procedure. The Column Name is changed from "Name" to "ContactPersonName" and also Data Type for "Name" is changed from NVARCHAR to VARCHAR and Data Type for "PersonType" is changed from NCHAR to VARCHAR while displaying the result set. This feature can be very useful when executing a stored procedure in SSIS where you can execute the Stored Procedure with the required columns names and appropriate data types.

The below image shows the output when running the above commands. The first result set doesn't change the names or data types and the second result set we have changed the column names and data types using this new feature.

using the with results feature of sql server denali
Next Steps
  • Download SQL Server 2012 to work with its new features

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, April 19, 2011 - 8:29:33 AM - Jeremy Kadlec Back To Top (13634)


I am not exactly sure what you mean.  I think a stored procedure is more than reasonable based on the example.

Thank you,
Jeremy Kadlec

Wednesday, April 13, 2011 - 4:52:41 PM - Anil Das Back To Top (13608)

Why are you using a stored procedure instead of a function in the first place. Yes, it shows off the feature, but your example is contrived.

Tuesday, April 12, 2011 - 9:10:32 AM - Sibi Back To Top (13596)

Good one Ashish... Awaiting more such tips...

get free sql tips
agree to terms