WITH RESULT SETS UNDEFINED

In SQL SERVER 2012 the EXECUTE statement is enhanced to specify the WITH RESULT SETS options. With this new option we can change the name and data type of the column’s of the returning result set.

To understand this new feature let us create a Database with table and stored procedures as show below:

CREATE DATABASE SQLHINTSDEMO
GO
USE SQLHINTSDEMO
GO
CREATE TABLE dbo.Customer
(CustomerId INT IDENTITY(1,1) NOT NULL,
CustomerName VARCHAR(100))
GO
INSERT INTO dbo.Customer(CustomerName)
VALUES('Basavaraj Biradar'),
('ShreeGanesh Biradar')
GO

-- Stored Procedure which returns single result set
CREATE PROCEDURE dbo.GetCustomerDetails
AS
BEGIN
SELECT CustomerId, CustomerName
FROM dbo.Customer WITH(NOLOCK)
END
GO

-- Stored Procedure which returns multiple result sets
CREATE PROCEDURE dbo.GetMultipleResultSet
AS
BEGIN
SELECT CustomerId, CustomerName
FROM dbo.Customer WITH(NOLOCK)

SELECT CustomerName, CustomerId
FROM dbo.Customer WITH(NOLOCK)
END
GO

--Stored Procedure with single result set with no record
CREATE PROCEDURE dbo.WithResultSetWithoutAnyRecord
AS
BEGIN
SELECT CustomerId, CustomerName
FROM dbo.Customer WITH(NOLOCK)
WHERE 1=2
END
GO

--Stored Procedure without result set as PRINT statement result 
--is not considered as result set instead it is message
CREATE PROCEDURE dbo.WithNoResultSet
AS
BEGIN
	PRINT 'NO RESULT SET'
END
GO

EXECUTE Statement WITH RESULT SETS NONE Option:

This option specifies that the execute statement will not return any results. If any results are returned it raises an exception and the batch is aborted.

Example 1: The below statement succeeds as the stored procedure WithNoResultSet is not returning any result set. Stored Procedure has only PRINT statement and the PRINT statement response is considered as a message.

EXECUTE dbo.WithNoResultSet
WITH RESULT SETS NONE;

RESULT:
NO RESULT SET

Example 2: The below statement fails as the stored procedure returns a result set even though the result set doesn’t have any records.

EXECUTE dbo.WithResultSetWithoutAnyRecord
WITH RESULT SETS NONE;

RESULT:

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

Example 3: The below statement fails as the stored procedure returns a result set.

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS NONE;

RESULT:

Msg 11535, Level 16, State 1, Procedure GetCustomerDetails, Line 8
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 UNDEFINED Option:

RESULT SETS UNDEFINED is the default option of the EXECUTE statement if this option is not specified.

Example 1: Examples of EXECUTE statement with WITH RESULT SETS UNDEFINED Option

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS UNDEFINED;
GO

RESULT:

CustomerId  CustomerName
----------- ----------------------------
1           Basavaraj Biradar
2           ShreeGanesh Biradar

EXECUTE dbo.WithNoResultSet
WITH RESULT SETS UNDEFINED;
GO

RESULT:
NO RESULT SET

Example 2: Examples of EXECUTE statement without WITH RESULT SETS UNDEFINED Option

EXECUTE dbo.GetCustomerDetails
GO

RESULT:
CustomerId CustomerName
———– ——————–
1 Basavaraj Biradar
2 ShreeGanesh Biradar

(2 row(s) affected)

EXECUTE dbo.WithNoResultSet
WITH RESULT SETS UNDEFINED;
GO

RESULT:
NO RESULT SET

EXECUTE Statement WITH RESULT SETS Option to redefine result sets:

Example 1: EXECUTE Statement WITH RESULT SETS Option to redfine single result. Here the stored procedure returned column names CustomerId and CustomerName are renamed to Id and Name. And also the CustomerId column datatype is converted from INT to VARCHAR(8).

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS (	
		(Id VARCHAR(8) NOT NULL, Name VARCHAR(100))
)
GO

RESULT
Id Name
——– ———————-
1 Basavaraj Biradar
2 ShreeGanesh Biradar
(2 row(s) affected)

Example 2: EXECUTE Statement WITH RESULT SETS Option to redefine two result sets

EXECUTE dbo.GetMultipleResultSet
WITH RESULT SETS (
---Redefine First Result Set
(CustomerId INT NOT NULL, CustomerName Varchar(100)), 
--comma separates the result sets definition

---Redefine Second Result Set
(Name Varchar(10), Id INT NOT NULL)
)
GO

RESULT:

CustomerId  CustomerName
----------- -------------------------
1           Basavaraj Biradar
2           ShreeGanesh Biradar

(2 row(s) affected)

Name                      Id
------------------------- -----------
Basavaraj Biradar         1
ShreeGanesh Biradar       2

(2 row(s) affected)

Example 3: WITH RESULT SETS option will not allow to have less or more no. of columns in the result re-definition than the no. of columns returned by the stored procedure. In below examples the stored procedure is returning to columns but while redefining the result set only one column is mentioned.

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS (
    (CustomerId INT NOT NULL)
)
GO

RESULT:

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

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS (
     (CustomerName VARCHAR(100))
)
GO

RESULT:

Example 4: While redefining the column data type, it will allow only to the compatible datatype. In the below example the CustomerName column type is VARCHAR but while redefiningin we are defining it as INT. So it will raise error in this case.

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS (
    (CustomerId INT NOT NULL, 
     CustomerName INT)
)
GO

RESULT:

Msg 8114, Level 16, State 2, Procedure GetCustomerDetails, Line 8
Error converting data type varchar(100) to int.

SqlHints.com

By Basavaraj Biradar

Tag Archives: WITH RESULT SETS UNDEFINED

EXECUTE WITH RESULT SETS IN SQL SERVER 2012

Share this: