EXECUTE Statement Enhancement WITH RESULT SETS in SQL Server 2012

Introduction

SQL Server 2012 comes with an extension, EXECUTE. We can specify the WITH RESULT SETS option with an EXECUTE statement. With this new feature we can change the name and data type of the columns of the result set of a Stored Procedure. WITH RESULT SETS is a very useful feature when anyone wants to display the result of a Stored Procedure by changing the name and data type of a column of a result set.

Syntax

EXEC Store_Procedure_Name
WITH RESULT SETS
((
ColumnName DataType,
………
………
ColumnName DataType
))

Example

To understand the new feature, let us use an example of a Sale Order. Suppose I have table, SALESORDER, containing order id, customer code, order date, total amount and Tax amount columns. I have a Stored Procedure that returns all rows from a sales table.

CREATE TABLE SALESORDER
(
OrderId INT NOT NULL,
CustomerCode VARCHAR(20),
OrderDate DATE,
TotalAmount MONEY,
TAXAmount MONEY
)

INSERT INTO SALESORDER VALUES (1000,'A0002','2014-01-02',2300,23),
(1001,'A0002','2014-01-03',2350,23.5),
(1002,'A0003','2014-01-04',2650,26.50),
(1003,'A0004','2014-01-04',3300,33),
(1004,'A0006','2014-01-05',4300,43),
(1005,'A0002','2014-01-08',5300,53),
(1006,'A0005','2014-01-10',2800,28)

CREATE PROCEDURE GetSalesOrder
AS
BEGIN
SELECT OrderId,CustomerCode,OrderDate,TotalAmount FROM SALESORDER
END

Output of the Stored Procedure without “WITH RESULT SETS”:

stored procedure without WITH RESULT SETS

In the preceding example we want to return the column names OrderId, CustCode, OrderDate and Amount. And also I want to change the data type of the OrderDate and TotalAmount fields.

EXEC GetSalesOrder
WITH RESULT SETS
((
OrderId INT,
CustCode VARCHAR(20),
OrderDate VARCHAR(10),
Amount FLOAT
))

change data type

EXECUTE Statement WITH RESULT SETS UNDEFINED Option

"WITH RESULT SETS UNDEFINED" is the default option of the EXECUTE statement.

EXEC GetSalesOrder
WITH RESULT SETS UNDEFINED

EXECUTE Statement WITH RESULT SETS

EXECUTE Statement WITH RESULT SETS NONE Option

The "WITH RESULT SETS NONE" option specifies that the execute statement will not return a result.

CREATE PROCEDURE NoResult
AS
BEGIN
Print 'This is Test'
END
EXEC NoResult
WITH RESULT SETS NONE

If a Stored Procedure returns a result than the Execute statement throws an exception.

EXEC GetSalesOrder
WITH RESULT SETS NONE

Msg 11535, Level 16, State 1, Procedure GetSalesOrder, Line 4
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.

EXECUTE Statement WITH RESULT SETS NONE Option

The “WITH RESULT SETS” option will not allow us to have fewer or more columns in the redefining result set of the Stored Procedure. It will throw an exception.

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

The “WITH RESULT SETS” option will allow only to a compatible data type when we redefine the data type. In the example above if I redefine the data type of the orderDate field then it throws the exception.

Msg 11538, Level 16, State 1, Procedure GetSalesOrder, Line 4
EXECUTE statement failed because its WITH RESULT SETS clause specified type 'int' for column #3 in result set #1, and the corresponding type sent at run time was 'date'; there is no conversion between the two types.

WITH RESULT SETS option

The following shows use of “WITH RESULT SETS” defining two results sets:

CREATE PROCEDURE GetSalesOrderTwoResultSet

BEGIN

SELECT OrderId,CustomerCode,OrderDate,TotalAmount FROM SALESORDER

SELECT OrderId,OrderDate,TAXAmount FROM SALESORDER

END

EXEC GetSalesOrderTwoResultSet

WITH RESULT SETS

(

OrderId INT,

CustCode VARCHAR(20),

OrderDate VARCHAR(10),

Amount FLOAT

(

OrderId INT,

OrderDate VARCHAR(10),

TAX FLOAT

)

Output

Output

Conclusion

The extension of the EXECUTE statement, "WITH RESULT SETS", is a very nice feature provided with SQL Server 2012. Using the "WITH RESULT SETS" feature we can change the names and data type of a returned result set.

EXECUTE Statement Enhancement WITH RESULT SETS in SQL Server 2012

Reactions

Comments