WITH RESULT SETS - New Feature in SQLServer 2012

Microsoft introduces new feature in SQL Server 2012 named WITH RESULT SETS. It is used to change the data type and column name of the result set which are returned from the stored procedure execution.

In earlier version of SQL Server, we achieved the same by inserting the result set into temporary table and then select it from the temporary table which is time consuming and lengthy process. But in SQL Server 2012, it is optimized by using WITH RESULT SETS. It also improves the query performance.

Let’s walk through the example.

First let’s create a sample table and insert some records on it.

USE [EmployeeDB]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee_Report]') AND type in (N'U'))

DROP TABLE [dbo].[Employee_Report]

GO

CREATE TABLE [dbo].[Employee_Report]

(

 [ID]                                                     [INT]   IDENTITY(1,1) ,

 [FirstName]                                         NVARCHAR(100) NULL,

 [LastName]                                         NVARCHAR(100)  NULL,

 [Dept]                                                 NVARCHAR(30) NULL,

 [Shift_Start_Time]                              TIME NULL,

 [Shift_END_Time]                               TIME NULL,

 [Joining_DATE]                                   DATE NULL

)

GO

INSERT INTO  [dbo].[Employee_Report] VALUES('MADAN','RAJ','IT','09:00 AM', '06:00 PM', '01-JAN-2011')

INSERT INTO  [dbo].[Employee_Report] VALUES('MICHAEL','AUSTIN','FINANCE','09:00 AM', '06:00 PM', '01-NOV-2010')

INSERT INTO  [dbo].[Employee_Report] VALUES('ROSHAN','JACK','HR','09:00 AM', '06:00 PM', '14-FEB-2009')

INSERT INTO  [dbo].[Employee_Report] VALUES('KARTHICK','KUMAR','IT','09:00 AM', '06:00 PM', '05-MAR-2011')

INSERT INTO  [dbo].[Employee_Report] VALUES('DEEPAK','BABU','FINANCE','09:00 AM', '06:00 PM', '23-APR-2011')

INSERT INTO  [dbo].[Employee_Report] VALUES('JOSEPTH','PATIL','IT','09:00 AM', '06:00 PM', '01-JUL-2011')

Now let’s create a stored procedure and execute & compare the result set, with and without using WITH RESULT SETS option.

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

DROP PROCEDURE [dbo].[SP_Test_WITHResultSets]

GO

CREATE PROCEDURE SP_Test_WITHResultSets

AS

BEGIN

 SELECT

  ID,

  FirstName + ' ' + LastName AS Name,

  Dept

 FROM [dbo].[Employee_Report]

END

GO

-- Execute the Stored Procedure in Normal mode

EXEC SP_Test_WITHResultSets

GO

-- Execute the Stored Procedure using WITH RESULTSETS

EXEC SP_Test_WITHResultSets

WITH RESULT SETS

(

 (

  Emp_ID INT,

  Emp_Name VARCHAR(200),

  Emp_Department VARCHAR(50)

 )

)

GO

The below snapshot shows the difference between normal result set and by WITH RESULT SETS option. We can find that data type and column names are modified using WITH RESULT SETS option.

Stored Procedure with Multiple Result set using WITH RESULT SETS option

The below example shows that how multiple result sets that are returned by stored procedure can be used with WITH RESULT SET Option.

-- Stored Procedure with multiple Result Set

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

DROP PROCEDURE [dbo].[SP_Test_WITHResultSets_Multi]

GO

CREATE PROCEDURE SP_Test_WITHResultSets_Multi

AS

BEGIN

 SELECT

  ID,

  FirstName + ' ' + LastName AS Name,

  Dept

 FROM [dbo].[Employee_Report]

 SELECT

  ID,

  Shift_Start_Time,

  Shift_END_Time,

  Joining_DATE

 FROM [dbo].[Employee_Report]

END

GO

-- Execute the Stored Procedure using WITH RESULTSETS

EXEC SP_Test_WITHResultSets_Multi

WITH RESULT SETS

(

 (

  Emp_ID INT,

  Emp_Name VARCHAR(200),

  Emp_Department VARCHAR(50)

 ) ,

 (

  Emp_ID INT,

  Work_Start_Time TIME,

  Work_End_Time TIME,

  Date_Of_Joining DATETIME

 )

)

GO

The below snapshot shows the result set of multiple result set  that are returned by stored procedure execution by WITH RESULT SETS option.