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.

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…

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.

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)...

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s