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.
