What is RESULT SETS?12
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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: microsoft sql server, T-SQL
12 thoughts on “What is RESULT SETS?”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] Kenneth Fisher learns and teaches us about RESULT SETS: […]
thanks for topic 🙂
Glad you liked it!
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.
Very cool. I’ve run into that problem before. I’ll have to remember that.
Apparently this is a SQL 2012+ feature. We’re still stuck on 2008 R2 and when I tried the examples – epic fail. 😉
I’m afraid so. It’s a new option in 2012.
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.
Great idea. I’ll have to remember that in the future.
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.
Based on what the two Steves said it looks like it should work great 🙂
[…] of elements in a set, time complexity constant O(1) must always be set. An intersection between two sets results in a third set with all of the elements present in both sets. The elements from both sets are […]