If you encounter error Msg 11537, Level 16 in SQL Server, chances are that you’re trying to execute a stored procedure by using the WITH RESULT SETS clause, but you haven’t included all the columns in your definition.

When you use the WITH RESULT SETS clause in the EXECUTE/EXEC statement, you must provide a definition for all columns returned by the stored procedure. If you don’t, you’ll get this error.

Example

The following stored procedure returns three columns.

EXEC sp_getCityById @CityId = 1;

Result:

+------------+----------------------------+-----------------------------+
| CityName   | LatestRecordedPopulation   | ValidFrom                   |
|------------+----------------------------+-----------------------------|
| Aaronsburg | 613                        | 2013-01-01 00:00:00.0000000 |
+------------+----------------------------+-----------------------------+

Now, suppose we want to redefine some of the columns. We can use the WITH RESULT SETS clause to do that.

Problem Code

But if we don’t include all three columns in this clause, we’ll get an error.

EXEC sp_getCityById @CityId = 1
    WITH RESULT SETS   
    (  
        (
            [City] nvarchar(50),
            [Population] int
        )
    );

Result:

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

The error message tells us how many columns we specified, and how many were sent by the stored procedure.

In this case, we specified two columns but the procedure sent three.

Good Code

We can fix this problem by including the third column.

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

Result:

------------+--------------+--------------+
| City       | Population   | Valid From   |
|------------+--------------+--------------|
| Aaronsburg | 613          | 2013-01-01   |
+------------+--------------+--------------+