If you encounter error Msg 11535, Level 16 while trying to execute a stored procedure, it’s because you didn’t define enough result sets in the WITH RESULT SETS clause.

Some stored procedures return multiple result sets. When using the WITH RESULT SETS clause, you need to define each expected result set. You need to do this even if you only want to change the definition of one or some of the result sets.

To fix this error, simply add the additional result sets to the WITH RESULT SETS clause, each separated by a comma.

You could also fix it by removing the WITH RESULT SETS clause, but I’ll assume you’re using it for a reason (i.e. you need to redefine the result set returned by the procedure).

Example of Code that Causes the Error

Suppose we have a stored procedure that returns three result sets.

EXEC sp_getCityStateCountryByCityId @CityId = 1;

Result:

+------------+----------------------------+-----------------------------+
| CityName   | LatestRecordedPopulation   | ValidFrom                   |
|------------+----------------------------+-----------------------------|
| Aaronsburg | 613                        | 2013-01-01 00:00:00.0000000 |
+------------+----------------------------+-----------------------------+
(1 row affected)
+---------------------+---------------------+----------------------------+
| StateProvinceCode   | StateProvinceName   | LatestRecordedPopulation   |
|---------------------+---------------------+----------------------------|
| PA                  | Pennsylvania        | 13284753                   |
+---------------------+---------------------+----------------------------+
(1 row affected)
+-----------------+---------------+----------------------------+
| IsoAlpha3Code   | CountryName   | LatestRecordedPopulation   |
|-----------------+---------------+----------------------------|
| USA             | United States | 313973000                  |
+-----------------+---------------+----------------------------+
(1 row affected)

And say we want to use the WITH RESULT SETS clause to redefine the columns of only the first result set.

Doing it with the following code will produce an error.

EXEC sp_getCityStateCountryByCityId @CityId = 1
WITH RESULT SETS   
(  
    (
        [City] nvarchar(50),
        [Population] int,
        [Valid From] date
    )
);

Result:

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

This is because we didn’t include the other result sets in the WITH RESULT SETS clause.

The Solution

The solution is to include the other result sets in the WITH RESULT SETS clause.

EXEC sp_getCityStateCountryByCityId @CityId = 1
WITH RESULT SETS   
(  
    (
        [City] nvarchar(50),
        [Population] int,
        [Valid From] date
    ),
    (
        [State Code] nvarchar(5),
        [State Name] nvarchar(50),
        [Population] int
    ),
    (
        [Country Code] nvarchar(3),
        [Country Name] nvarchar(60),
        [Population] int
    )
);

Result:

+------------+--------------+--------------+
| City       | Population   | Valid From   |
|------------+--------------+--------------|
| Aaronsburg | 613          | 2013-01-01   |
+------------+--------------+--------------+
(1 row affected)
+--------------+--------------+--------------+
| State Code   | State Name   | Population   |
|--------------+--------------+--------------|
| PA           | Pennsylvania | 13284753     |
+--------------+--------------+--------------+
(1 row affected)
+----------------+----------------+--------------+
| Country Code   | Country Name   | Population   |
|----------------+----------------+--------------|
| USA            | United States  | 313973000    |
+----------------+----------------+--------------+
(1 row affected)

The problem has now been fixed.