Advertisements
Home > SQL Server 2012, Stored Procedures > SQL Server 2012 (a.k.a Denali) – New feature | WITH RESULT SETS

SQL Server 2012 (a.k.a Denali) – New feature | WITH RESULT SETS


“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

Advertisements
  1. Anil
    April 10, 2013 at 12:27 pm

    Hello Manoj bhai, I have a query in my mind what i would like to ask. Manoj bhai can we execute a procedure with sql statement.
    Like
    Exec Proc_Name (select param1,param2,param3….. from table_name)

    Hopefully u will help me out..
    Thank you

  2. André Pelser
    January 20, 2013 at 1:09 pm

    Hi Manoj

    Well your comment is OK but not very thoughtful for me as a junior.
    I was hoping you would use your brain matter to find ways to add/remove the columns and/or concatenate them, like FirstName & LastName to FullName but NO!!!

    Instead you blame MS for not thinking for you:

    — Create a table with columns with required names:

    CREATE TABLE #tempData (
    Level INT,
    BusinessID INT,
    EmployeeFirstName VARCHAR(50),
    EmployeeLastName VARCHAR(50),
    EmployeeFullName AS (CONCAT(EmployeeFirstName, ‘ ‘ + EmployeeLastName)),
    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 [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100

    — Finaly select the table and use the records:
    SELECT * –Or the columns you would want
    FROM #tempData

    — Final Cleanup:
    DROP TABLE #tempData
    GO

    • January 21, 2013 at 12:27 pm

      Hi Andre, thanks for your comments.

      Yes, you are right and the workaround you provided will work.

      I’m not blaming anyone, I mentioned in the above post that when I knew about this feature I was thinking this feature will also be there, as lot of time I had to struggle about it.

      Also I can’t blame MS, as I work there and with this product 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: