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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL