Overview of WITH RESULT SETS Feature of SQL Server

Problem

SQL Server 2012 introduced a new feature WITH RESULT SETS. This allows changing the names and data types of the result set of a stored procedure. In this tip, we look at an example which uses the SQL WITH RESULT SETS feature in SQL Server.

Solution

There was a limitation in previous versions of SQL Server to change a Column Name or a Data Type for the result set of a Stored Procedure. To do this, you would need make changes to all the references within the Stored Procedure. This limitation has been overcome with the release of the WITH RESULT SETS feature in SQL Server.

Let’s look at an example which demonstrates using WITH RESULT SETS syntax to change the names and data types.

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

In this example, using WITH RESULTS SETS we can change the output for Column Name and Data Type.

We are doing the following:

• Change Column Name from “Name” to “ContactPersonName“.
• Change Data Type for “Name” from NVARCHAR to VARCHAR.
• Change Data Type for “PersonType” from NCHAR to VARCHAR..

This can be useful when executing a stored procedure to return the required columns names and data types.

The below image shows the output when running the above commands. The first result set doesn’t change names or data types and the second result changes names and data types.

Next Steps

• Use the SQL with result sets in your code as needed
• Check out these related articles:
  • SQL Server Stored Procedure Tutorial
  • Return data from a stored procedure