Words: 567
Time to read: ~ 3 minutes
Introduction:
Have you ever run into an issue where you’ve had to call the output columns of a stored procedure something different, ever tried to rename them? It’s painfully manual, right?
You have the create a table with the correct data types and in the correct column positions, execute the stored procedure while inserting into that table, and then select the results out again!
And that’s not even going to work if you have more than 1 result set returning from your stored procedure!?
The point of this blog post is to show you how to quickly rename the columns from a stored procedure and we’re going to be doing this using WITH RESULT SETS.
Isn’t She Lovely:
I’m running this on Microsoft SQL Server 2014 (SP1-CU13) and I’m running the following code as examples.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE tempdb; | |
GO | |
IF OBJECT_ID(N'dbo.Header2', N'U') IS NOT NULL DROP TABLE dbo.Header2; | |
GO | |
CREATE TABLE dbo.Header2 ( | |
Id TINYINT NOT NULL CONSTRAINT PK_Header2_Id PRIMARY KEY, | |
CreateDate DATE NOT NULL CONSTRAINT DF_Header2_CreateDate_SYSDATETIME DEFAULT SYSDATETIME(), | |
RandomData CHAR(52) NOT NULL | |
); | |
GO | |
IF OBJECT_ID(N'dbo.Header', N'U') IS NOT NULL DROP TABLE dbo.Header; | |
GO | |
CREATE TABLE dbo.Header ( | |
Id TINYINT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Header_Id PRIMARY KEY, | |
CreateDate DATE NOT NULL CONSTRAINT DF_Header_CreateDate_SYSDATETIME DEFAULT SYSDATETIME(), | |
RandomData CHAR(50) NOT NULL CONSTRAINT DF_Header_RandomData_X DEFAULT (REPLICATE('X', 50)) | |
); | |
GO | |
INSERT INTO dbo.Header2 (Id, RandomData) | |
SELECT Id, REPLICATE('Y', Id) | |
FROM ( | |
INSERT INTO dbo.Header (CreateDate) | |
OUTPUT inserted.CreateDate, inserted.Id, inserted.RandomData | |
SELECT TOP (50) SYSDATETIME() FROM master.dbo.spt_values | |
) AS DataInsert | |
WHERE DataInsert.Id % 2 = 0; — Only insert into dbo.Header2 when the Id is a multiple of 2. | |
GO | |
ALTER TABLE dbo.Header2 ADD CONSTRAINT FK_Header2_Header_Id_Id FOREIGN KEY (Id) REFERENCES dbo.Header(Id); | |
GO | |
SELECT * FROM dbo.Header; | |
SELECT * FROM dbo.Header2; | |
GO | |
IF OBJECT_ID(N'dbo.GetHeaders', N'P') IS NOT NULL DROP PROCEDURE dbo.GetHeaders; | |
GO | |
CREATE PROCEDURE dbo.GetHeaders (@Id tinyint) | |
AS | |
SELECT h.Id, h.CreateDate, h.RandomData | |
FROM dbo.Header AS h | |
WHERE h.Id = @id; | |
SELECT h2.Id, h2.CreateDate, h2.RandomData | |
FROM dbo.Header2 AS h2 | |
WHERE h2.Id = @Id; | |
GO |
Signed, Sealed, Delivered:
As I said, we’re going to be using WITH RESULT SETS to rename the output columns of a stored procedure.
From the documents, we just have to specify the column names and the data types, the rest are optional.
So taking our stored procedure, let’s rename the results to show what table they’ve come from! We’ve got 2 results so we’ll need to create 2 result sets…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
EXECUTE dbo.GetHeaders @Id = 6 | |
WITH RESULT SETS ( | |
( — Header data Set…Why not rename these columns… | |
HeaderId tinyint NOT NULL, | |
HeaderCreatedDate date NOT NULL, | |
HeaderRandomness char(50) NOT NULL | |
), | |
( — Header2 data set… | |
Header2Id tinyint NOT NULL, | |
Header2CreatedDate date NOT NULL, | |
Header2Randomnessness char(52) NOT NULL | |
) | |
); | |
GO |
Which gives us the lovely result (sets) of:

And it works!
Our columns Id
, CreateDate
, and RandomData
in the first result set get a prefix of Header
and a prefix of Header2
in the second result set.
Uptight:
SURPRISE!
Now imagine my surprise when, going through these examples again, I run the code and receive an error message!
Msg 11537, Level 16, State 1, Procedure GetHeaders, Line 3
EXECUTE statement failed because its WITH RESULT SETS clause specified 3 column(s) for result set number 2, but the statement sent 1 column(s) at run time.

I know that it’s a different Id but I should still get a result set. It should just be empty.
So I run through my checklist
- Have I missed a previous step? No.
- Have I messed up a previous step? No.
- Have I run things out of order. No.
- Have I accidently only run part of the script. No.
- What’s the error message saying?
Difference in columns? Only 1 column returned? That’s not making any sense.
I quickly write out the two SELECT statements and run them separately. They execute without problems.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT h.Id, h.CreateDate, h.RandomData | |
FROM dbo.Header AS h | |
WHERE h.Id = 5 | |
SELECT h2.Id, h2.CreateDate, h2.RandomData | |
FROM dbo.Header2 AS h2 | |
WHERE h2.Id = 5 |

I start switching through the different panels and suddenly something pops out at me from the Messages pane.
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)

For Once In My Life:
In case you’re wondering, I’m not running the SELECT statements twice. Here’s the same messages with reference to what is causing them:
(1 row(s) affected) <– dbo.Header
(1 row(s) affected) <– Actual Execution Plan
(0 row(s) affected) <– dbo.Header2
(1 row(s) affected) <– Actual Execution Plan
I turn off Actual Execution Plans and everything is working again!
Master Blaster:
Apart from OutOfMemory exception when you leave Actual Execution Plans on and then run a loop, this is the first time I’ve seen them interfere with a T-SQL statement!
In case you are worried about this happening to you, don’t be!
I was double checking this by runnning it on Microsoft SQL Server 2016 (SP1) and I tried to verify the behaviour.
Nope! No problem! That’s the second thing that I’ve found naturally that’s been fixed in newer versions of SQL Server!