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

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 1
Error 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;
GO

IF OBJECT_ID('dbo.GetOrderInfo', 'P') IS NOT NULL DROP PROC dbo.GetOrderInfo;
GO

CREATE PROC dbo.GetOrderInfo
  @orderid AS INT
AS

SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = @orderid;

SELECT SalesOrderID, SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @orderid;
GO

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

EXEC dbo.GetOrderInfo @orderid = 43671
WITH 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 = 43671
WITH 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 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.<

Cheers,

BG

Discuss this Blog Entry 2

on Mar 4, 2011
I was wondering where this would be useful.
The only place I can imagine is with a SSIS package, where SSIS currently gets confused about the returing meta data, if you use a #Temp table.

Am I missing an important other area, where this is useful?




on Mar 1, 2011
It would be a really nice feature if this could be combined with this request

http://connect.microsoft.com/SQLServer/feedback/details/470881/allow-more-than-one-resultset-to-be-stored-with-insert-into-exec-syntax



Please or Register to post comments.

What's Puzzled By T-SQL Blog?

T-SQL tips and logical puzzles from Itzik Ben-Gan.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×