Temporarily Renaming Stored Procedure Columns

With an error surprise!

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.


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

view raw

TemporarilyRenamingStoredProcedureColumns.sql

hosted with ❤ by GitHub

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…


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

view raw

FirstResultsSet.sql

hosted with ❤ by GitHub

Which gives us the lovely result (sets) of:

HowItIsSupposedToWork
Here I am baby!

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.

ThisShouldBeRight
I’m a poor man’s son

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.


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

view raw

JustTheSelects.sql

hosted with ❤ by GitHub

JustTheSelects
Plain out of sight!

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)

MessagesPane
Not “Out of sight!”

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!

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

Leave a Reply

%d bloggers like this: