WITH RESULT SETS Feature of SQL Server Denali

WITH RESULT SETS is a very useful feature especially when one needs to display a result set of a stored procedure by changing the names and data types of the returning result set. 


There was always a limitation in the previous versions of SQL Server that whenever you wanted to change a Column Name or a Data Type within the result set of a Stored Procedure you ended up making changes to all the references within a Stored Procedure. This limitation has been overcome with the release of WITH RESULT SETS feature in SQL Server Denali. 


USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee]
(
 [ID]   [INT]   NOT NULL,
 [EmployeeType]  [NCHAR](15)  NOT NULL,
 [FirstName]  NVARCHAR(50) NOT NULL,
 [MiddleName]  NVARCHAR(50)  NULL,
 [LastName]  NVARCHAR(50)  NOT NULL,
) ON [PRIMARY]
GO
INSERT INTO dbo.Employee VALUES (1,'Cashier','Lee','L','Warne')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsingWithResultSetsFeatureOfDenali]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UsingWithResultSetsFeatureOfDenali]
GO
CREATE PROCEDURE UsingWithResultSetsFeatureOfDenali
AS
BEGIN
 SELECT
  ID, 
  FirstName + ' ' + MiddleName +' '+ LastName AS Name, 
  EmployeeType 
 FROM dbo.Employee
END
GO
/* Execute Stored Procedure */
EXEC UsingWithResultSetsFeatureOfDenali
GO
/* Using WITH Result Sets Feature Of Denali (SQL Server 2011) */
EXEC UsingWithResultSetsFeatureOfDenali 
WITH RESULT SETS
(
 ( 
  ID INT,
  EmployeeName VARCHAR(150),
  EmployeeType VARCHAR(15)
 ) 
) 
GO



In the above example, you can see that using WITH RESULTS SETS feature of SQL Server Denali we have changed the Column Name and Data Type to meet our needs irrespective of the Column Name and Data Type returned within the result set of the Stored Procedure.  The Column Name is changed from “Name” to “EmployeeName” and also Data Type for “Name” is changed from NVARCHAR to VARCHAR and Data Type for “EmployeeType” is changed from NCHAR to VARCHAR while displaying the result set. This feature can be very useful when executing a stored procedure in SSIS where you can execute the Stored Procedure with the required columns names and appropriate data types.