Usage and Benefits of Using WITH RESULT SETS In SQL Server 2012

Introduction

It is not uncommon to have stored procedures returning result sets, and  although it sounds like a good idea, it becomes a problem when you want to have control of the name of the columns and data type of the columns being returned as part of the result set. SQL Server 2012 (Code name SQL Denali) introduces a new feature WITH RESULT SETS which lets you redefine the name and data types of the columns being returned from the stored procedure. This article talks about this new feature in detail.

Understanding WITH RESULT SETS clause

SQL Server 2012 (Code name SQL Denali) introduces the WITH RESULT SETS clause with the EXECUTE statement, which lets you redefine the name and data types of the columns being returned from the stored procedure. This comes in very handy when you want to insert the records coming in a result set to a temporary table with a specific column name and data type and you don’t want to rely on what is being returned by the stored procedure.

The WITH RESULT SETS clause can also be used with a stored procedure, which returns multiple result sets and for each result set you can define the column name and data types for each column separately.

Please note:

• Sometimes if you want to restrict a stored procedure to return a result set you can use the RESULT SETS NONE clause.
• The WITH RESULT SETS option cannot be specified in an INSERT…EXEC statement.
• The number of columns being returned as part of result set cannot be changed.

Enough of theory now, let’s do some hands on around this new feature.

Example – Usage

In the example below, I am creating a table and inserting some data to it; then I am creating a stored procedure, which selects data from the table and returns the result set. Next if you notice, the “EXECUTE GetEmployees call” returns the result set in the format (column name and data type) in the format specified in the body of the stored procedure and you don’t have any control to change it. Finally you can see the EXECUTE GetEmployees call with WITH RESULT SETS clause, which gives you a control on renaming the columns and defining a different data type (though compatible only) of each column of the result set being returned:

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]
(
        [EmpId]   [INT]   NOT NULL IDENTITY PRIMARY KEY,
        [FirstName]  NVARCHAR(100) NOT NULL,
        [MiddleName]  NVARCHAR(100)  NULL,
        [LastName]  NVARCHAR(100)  NOT NULL,
)
GO
INSERT INTO [dbo].[Employee] (FirstName, MiddleName, LastName)
VALUES
       ('Arshad', NULL,'Ali'),
       ('Paul', 'M','John')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEmployees]')   AND type in (N'P', N'PC'))
       DROP PROCEDURE [dbo].[GetEmployees]
GO
CREATE PROCEDURE GetEmployees
AS
BEGIN
 SELECT EmpId, FirstName + ' ' + ISNULL(MiddleName, '') +' '+ LastName AS Name
 FROM dbo.Employee
END
GO
EXECUTE   GetEmployees
GO
EXECUTE   GetEmployees 
WITH RESULT SETS
(
       (
       EmployeeId INT,
       EmployeeName VARCHAR(150)
       ) 
)
GO

Figure 1 – WITH RESULT SETS example with single result set

As I said before, not only you can use the WITH RESULT SETS clause with a stored procedure that returns a single result set, as shown above, but you can also use it with a stored procedure that returns multiple result sets, as shown below. In the example below you can see the stored procedure returns two result sets and the column name and data type of each column from each result set is being redefined separately.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEmployeesWithMultipleResultsets]') AND type in (N'P', N'PC'))
       DROP PROCEDURE [dbo].[GetEmployeesWithMultipleResultsets]
GO
CREATE PROCEDURE GetEmployeesWithMultipleResultsets
AS
BEGIN
 SELECT EmpId, FirstName + ' ' + ISNULL(MiddleName, '') +' '+ LastName AS Name
 FROM dbo.Employee
    SELECT EmpId, FirstName + ' ' + ISNULL(MiddleName, '') +' '+ LastName AS Name
 FROM dbo.Employee
END
GO
EXEC GetEmployeesWithMultipleResultsets
GO
EXECUTE   GetEmployeesWithMultipleResultsets 
WITH RESULT SETS
(
       (
       EmployeeId1 INT,
       EmployeeName1 VARCHAR(150)
       ), 
       (
       EmployeeId2 INT NOT NULL,
       EmployeeFullName2 VARCHAR(150) NOT NULL
       ) 
)
GO

Figure 2 – WITH RESULT SETS example with multiple result sets

Conclusion

In this article I talked about a new feature (WITH RESULT SETS), which can be used along with the EXECUTE statement to redefine the name and data types of the columns being returned as a result set from the stored procedure. This new feature can be used with a stored procedure that returns multiple result sets as well and each of these result sets can be redefined/formatted separately.

Resources

Review – EXECUTE (Transact-SQL)

See all articles by Arshad Ali