What is RESULT SETS?

11

January 14, 2016 by Kenneth Fisher

I was reading some code the other day and it included the statement RESULT SETS. I’d never seen it before so it seemed worth a quick look. I’ll warn you in advance this is somewhat of a BOL blog post. I’m basically taking what you can find in BOL and repeating it back with a little bit of interpretation. I try not to do this type of post often but I haven’t used this particular option before and haven’t found a great use for it yet. So consider this a blog post to point out a new option 🙂

First of all it’s part of the EXECUTE command.

Quick definition. A result set is the output of a query. It could result in a one row, one column output or a 100+ column, million+ row output. Either way that’s a result set. Note: you can have multiple result sets from a single object (stored procedure, function etc) call.

There are three options.

  • RESULT SETS UNDEFINED – This is the default and means that you don’t know what the result set will be.
  • RESULT SETS NONE – You expect (and require) that there will be no result set(s) returned.
  • RESULT SETS ( <result_sets_definition> ) – There will be result set(s) returned and you are going to specify the definition. The column names within the definition(s) can act as alias’ for the column names.

 
So what use is this? Well primarily it would protect your code from changes in the code that it’s calling. Specifically if you aren’t getting what you expected then throw an error. Every now and again you have code that you would rather fail than be wrong. This will help with that.

And here is the obligatory demo 🙂

-- Create a procedure to test on
CREATE PROCEDURE ResultSetsExample AS
SELECT database_id, name
FROM sys.databases
GO
-- All of these have almost identical output
EXEC ResultSetsExample
EXEC ResultSetsExample WITH RESULT SETS UNDEFINED -- Default option

-- The output of this last one has the column 
-- names aliased to [db_id] and [db_name]
EXEC ResultSetsExample WITH 
	RESULT SETS (([db_id] int, [db_name] varchar(100)))

But what happens when you change the output of the stored procedure?

ALTER PROCEDURE ResultSetsExample AS
SELECT database_id, name, owner_sid
FROM sys.databases
GO
EXEC ResultSetsExample WITH 
	RESULT SETS (([db_id] int, [db_name] varchar(100)))

Now we get an error.

Msg 11537, Level 16, State 1, Procedure ResultSetsExample, Line 13
EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.

Last but not least if you run this:

EXEC ResultSetsExample WITH RESULT SETS NONE

Because the SP call actually returns an output you get this error:

Msg 11535, Level 16, State 1, Procedure ResultSetsExample, Line 11
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.

11 thoughts on “What is RESULT SETS?

  1. […] Kenneth Fisher learns and teaches us about RESULT SETS: […]

  2. vinhvuthi says:

    thanks for topic 🙂

  3. Steve M. says:

    I havce found a great use for RESULTS SET’s in SSIS. executing a stored procedure as the source destination will have SSIS peek into it and assume the output. This can be a real problem if there is a temp table or CTE definded. By using RESULT SET, you can inform SSIS so that it does’n t try to read the stored procedure code and thus, no meta data errors if there is a change.

  4. lionfan1991 says:

    Apparently this is a SQL 2012+ feature. We’re still stuck on 2008 R2 and when I tried the examples – epic fail. 😉

  5. SteveO says:

    We use WITH RESULT SETS when calling a stored procedure from within a SQL Server Integration Services package. It’s needed so we can define the metadata of what is being returned from the stored procedure. Prior to SQL Server 2012, we used to be able to “read” the metadata from the stored procedure, but now we have to use WITH RESULT SETS to define the column information inside SSIS.

  6. SqlNigthOwl says:

    Hmmm. I wonder if this would be useful as a directive within an SSIS package? I’ve had SQL Tasks bomb because the final result set wasn’t visible/available. I know the procedure will return a select statement (result set), and what the columns would be. I’ll have to give it a try.

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: