Using WITH RESULT SETS Feature of SQL Server 2012

By: Editor
April 2, 2011

Introduction

Microsoft SQL Server 2012 extends the EXECUTE statement to introduce WITH RESULT SETS option which can be used to change the Column Name and Data Types of the result set returned by the execution of stored procedure.

Example Using WITH RESULT SETS Feature of SQL Server 2012

Let us go through an example which illustrates WITH RESULT SETS Feature of SQL Server 2012.

Use AdventureWorks2008R2
GO

IF
EXISTS (             
SELECT
* FROM sys.objects
               
WHERE object_id = OBJECT_ID(N'[dbo].[WithResultSets_SQLServer2012]')
               
AND type in (N'P', N'PC'))

DROP
PROCEDURE [dbo].[WithResultSets_SQLServer2012]
GO

CREATE PROCEDURE WithResultSets_SQLServer2012
AS

               
BEGIN
                               
SELECT
                                               
 TOP 5
                                               
 PP.FirstName + ' ' + PP.LastName AS Name
                                               
,PA.City
                                               
,PA.PostalCode
                               
FROM  Person.Address PA
                                               
INNER JOIN
                                                                             
Person.BusinessEntityAddress PBEA
                                                                             
ON PA.AddressID = PBEA.AddressID
                                               
INNER JOIN
                                                                             
Person.Person PP
                                                                             
ON PBEA.BusinessEntityID = PP.BusinessEntityID
                               
ORDER BY PP.FirstName
                END

GO



Once the stored procedure is created successfully. The next step will be to execute the above stored procedure using WITH RESULT SET Feature of SQL Server 2012.

/* Execute Stored Procedure which uses WITH RESULT SETS  Feature of SQL Server 2012*/

EXEC WithResultSets_SQLServer2012GO

/*
 
Example - Using WITH RESULT SETS Feature of SQL Server 2012
*/

EXEC
WithResultSets_SQLServer2012
WITH
RESULT SETS
(

 
(
 
[Employe Name] NVARCHAR(100),
 
[Employee City]                  NVARCHAR(20),
 
[Employee Postal Code]    NVARCHAR(30)
 
)

)

GO

WITH Result Set Feature SQL Server 2012

In the above image you could see that once you execute WithResultSets_SQLServer2012 stored procedure using WITH RESULT SET feature of SQL Server 2012 you can change the Column Name and Data Type as per your need without actually altering the exisiting stored procedure. In the second result set (above image) you could see that the Column Names are changed from Name to Employee Name, City to Employee City and PostalCode to Employee Postal Code. Similary, the data type was changes from VARCHAR to NVARCHAR.

Conclusion

The WITH RESULT SET Feature of SQL Server 2012 is a great enhancement to the EXECUTE Statement. This feature will be widely used by Business Intelligence Developers to execute a stored procedure with in an SQL Server Integration Services (SSIS) Package to return the result set with required Columns and modified data types.

Reference: EXECUTE (Transact-SQL)


Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article


Geeks who read this article also read…




Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."