This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

SQL Server 2012 T-SQL at a Glance – EXECUTE WITH RESULT SETS

Suppose that you need to write code against SQL Server that uses result sets returned from stored procedures and dynamic batches, and you need a guarantee that the result sets will have very specific metadata. In guarantee, I mean that if the shape of the result is different than what you expect, you need it to fail. So far, there was no real answer to this need. SQL Server 2012 (formerly code-named Denali) introduces a new option for the EXECUTE statement called RESULT SETS that is designed exactly for this purpose.

You can specify the new option with the EXECUTE statement when executing a stored procedure or a dynamic batch, like so:

EXECUTE WITH ;

There are three supported options:

1. RESULT SETS UNDEFINED: this is the default, meaning that never mind what’s the shape of the result sets, and whether there’s any result at all, there will be no error related to the shape of the result. Here’s an example:

EXEC('SELECT 43112609 AS val;')
WITH RESULT SETS UNDEFINED;

2. RESULT SETS NONE: this means that you have a guarantee that no result set will be returned. If a result set is returned, an error is generated and the batch terminates. Here’s an example for an error:

EXEC('SELECT 43112609 AS val;')
WITH RESULT SETS NONE;
Msg 11535, Level 16, State 1, Line 1
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.

And here’s an example for an errorless execution (note that the result of a PRINT command is not considered a result set):

EXEC('PRINT 43112609;')
WITH RESULT SETS NONE;

3. RESULT SETS: specify the metadata of one or more result sets, and get a guarantee that the result sets and their number will match the metadata defined in the RESULT SETS clause, or otherwise an error will be generated. Here’s a simple example first for an errorless execution:

EXEC('SELECT 43112609 AS val;')
WITH RESULT SETS
(
(
val INT
)
);

Note the two layers of brackets; the outer pair is for the RESULT SETS clause, and the inner pair is for a specific result set—you can specify multiple ones separated by commas.

Perhaps this will surprise you first, but the following is also an errorless execution:

The reason is that as long as implicit conversion works—and of course, in our case the value successfully converts to VARCHAR(10)—there’s no error. But when the value isn’t convertible, you do get an error. Try defining a SMALLINT type for the value:

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

You are probably wondering about all kinds of nuances and variations of possible mismatches, whether they will generate an error or not. For example, would a column name mismatch cause an error? No; because it’s like assigning a different alias to the result column. In fact, the result set will be returned with the column names defined in the RESULT SETS clause. Would a NULL violation cause an error? Yes. Of course, there are other cases you could be curious about, so I’ll leave you with playing with this new feature yourself, and as usual, recommend visiting the official documentation on the topic EXECUTE in SQL Server 2012's Books Online.<