Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – EXECUTE statement failed because its WITH RESULT SETS clause specified result set(s) and the statement tried to send more result sets than this.’

EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), and the statement tried to send more result sets than this is one of the new error messages introduced in SQL Server 2012. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS.

Let’s discuss this in detail:
Message Number: 11535

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), and the statement tried to send more result sets than this.

Error Generation:

Let me create a sample stored procedure to generate this error.

Create Procedure UDP_Resultset
As
Select 'A' as [First Letter]
Select 'B' as [Second Letter]
GO
EXEC UDP_Resultset

exec resultset1.1

You can view that the above stored procedure is executed successfully, and it has two result sets.

Lets try to execute it with WITH RESULT SETS clause.

EXEC UDP_Resultset
WITH RESULT SETS
(([1st Letter] varchar(50)
));
--OUTPUT

Msg 11535, Level 16, State 1, Procedure UDP_Resultset, Line 5
EXECUTE statement failed because its WITH RESULT SETS
clause specified 1 result set(s), and the statement tried
to send more result sets than this.
Ooopps…… I am unable to execute it properly. It returned the first result set and the error message.

exec resultset1.2

Resolution:
Why this error ? Because, the above stored procedure returns two result sets but we defined WITH RESULT SETS clause for only one result set. Lets rewrite the script and define two result sets (The same number of result sets returned by stored procedure) to avoid this error.

EXEC UDP_Resultset
WITH RESULT SETS
(([1st Letter] varchar(50)
)
,([2nd Letter] varchar(50)
));
--OUTPUT

exec resultset1.3

Conclusion :
Remember, whenever you use any stored procedure WITH RESULT SETS clause, make sure you use exactly the same number of result set returned by stored procedure to avoid such errors.

Read Full Post »