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.

Related: "SQL Server 2012 Contained Databases" and "Using SQL Server 2012 Contained Databases"

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 1EXECUTE 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:

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

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:

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

And this time the code fails:

Msg 8114, Level 16, State 2, Line 1Error converting data type int to smallint.

Next, let’s look at a more detailed example. Consider the following stored procedure:

SET NOCOUNT ON;USE AdventureWorks2008R2;GOIF OBJECT_ID('dbo.GetOrderInfo', 'P') IS NOT NULL DROP PROC dbo.GetOrderInfo;GOCREATE PROC dbo.GetOrderInfo  @orderid AS INTASSELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateIDFROM Sales.SalesOrderHeaderWHERE SalesOrderID = @orderid;SELECT SalesOrderID, SalesOrderDetailID, OrderQtyFROM Sales.SalesOrderDetailWHERE SalesOrderID = @orderid;GO

You execute the procedure with the RESULT SETS option defining two result sets:

EXEC dbo.GetOrderInfo @orderid = 43671WITH RESULT SETS(  (    SalesOrderID        INT      NOT NULL,    OrderDate           DATETIME NOT NULL,    TotalDue            MONEY    NOT NULL,    CurrencyRateID      INT          NULL  ),  (    SalesOrderID        INT      NOT NULL,    SalesOrderDetailID  INT      NOT NULL,    OrderQty            SMALLINT NOT NULL  ));

The number of result sets returned and their metadata match those you defined in the RESULT SETS clause, hence the code runs successfully:

SalesOrderID OrderDate               TotalDue              CurrencyRateID
------------ ----------------------- --------------------- --------------
43671        2005-07-01 00:00:00.000 9153.6054             NULL

SalesOrderID SalesOrderDetailID OrderQty
------------ ------------------ --------
43671        115                1
43671        116                2
43671        117                1
43671        118                2
43671        119                2
43671        120                2
43671        121                2
43671        122                2
43671        123                1
43671        124                1
43671        125                1

Try again; only this time, omit the CurrencyRateID column from the definition of the first result set:

EXEC dbo.GetOrderInfo @orderid = 43671WITH RESULT SETS(  (    SalesOrderID        INT      NOT NULL,    OrderDate           DATETIME NOT NULL,    TotalDue            MONEY    NOT NULL  ),  (    SalesOrderID        INT      NOT NULL,    SalesOrderDetailID  INT      NOT NULL,    OrderQty            SMALLINT NOT NULL  ));

And this time you get an error:

Msg 11537, Level 16, State 1, Procedure GetOrderInfo, Line 6EXECUTE 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.<

Cheers,

BG