SQL with Manoj

“WITH RESULT SETS”, a new feature added in SQL Server 2012 allows us to tweak the Column Names and their Datatypes returned by an SP upon executed as per our needs. In SQL Server 2012 while calling an SP you can add the “WITH RESULT SETS” option and can provide the new Column Names and/or with new Datatypes with the parenthesis as per your wish (shown below).

Prior to this if one has to do the same, he has to either:
– Edit the same SP with new column names, or
– Create a new duplicate SP with different column headers, or
– Push records to a temp table first then use the records set form that table (discussed in later part of this post).

Let’s check this new feature by a simple example here by using a Stored Procedure from [AdventureWorks2012] database for SQL Server 2012:

USE [AdventureWorks2012]
GO

-- Normal SP call without using "WITH RESULT SETS" option:
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100

Recordset with same headers as in SP:

RecursionLevel	BusinessEntityID	FirstName	LastName	OrganizationNode	ManagerFirstName	ManagerLastName
0		100			Lolan		Song		/3/1/9/7/		Kok-Ho			Loh
1		93			Kok-Ho		Loh		/3/1/9/			Peter			Krebs
2		26			Peter		Krebs		/3/1/			James			Hamilton
3		25			James		Hamilton	/3/			Ken			Sánchez

-- This is how you can use the new option and get desired headers:
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100
WITH RESULT SETS 
(
	(
	Level INT,
	BusinessID INT, 
	EmpFirstName VARCHAR(50), 
	EmpLastName VARCHAR(50), 
	OrgNode VARCHAR(20), 
	ManagerFirstName VARCHAR(50), 
	ManagerLastName VARCHAR(50)
	)
)

Recordset with new headers as in SP:

Level	BusinessID	EmpFirstName	EmpLastName	OrgNode		ManagerFirstName	ManagerLastName
0	100		Lolan		Song		/3/1/9/7/	Kok-Ho			Loh
1	93		Kok-Ho		Loh		/3/1/9/		Peter			Krebs
2	26		Peter		Krebs		/3/1/		James			Hamilton
3	25		James		Hamilton	/3/		Ken			Sánchez

–> Traditonal appraoch prior to ver. 2012

-- Create a table with columns with required names:
CREATE TABLE #tempData (
	Level INT,
	BusinessID INT, 
	EmployeeFirstName VARCHAR(50), 
	EmployeeLastName VARCHAR(50), 
	OrgNode VARCHAR(20), 
	ManagerFirstName VARCHAR(50), 
	ManagerLastName VARCHAR(50)
	)

-- Insert records from the SP to the table:
INSERT INTO #tempData (Level, BusinessID, EmployeeFirstName, EmployeeLastName, OrgNode, ManagerFirstName, ManagerLastName)
EXEC @return_value = [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100

-- Finaly select the table and use the records:
SELECT * FROM #tempData

-- Final Cleanup:
DROP TABLE #tempData
GO

Well this is OK, but not very exciting feature for me as a developer.
I was hoping this would allow me to add/remove the columns and/or Concatenate them, like FirstName & LastName to FullName, but No!!!

For more information you can check MS BOL, here: http://msdn.microsoft.com/en-us/library/ms188332(v=sql.110).aspx