SQL Server: sp_describe_first_result_set with more than one select in a stored procedure

All we need is an easy explanation of the problem, so here it is.

I’m using sp_describe_first_result_set to retrive metadata from a stored procedure and having the following error:

Msg 11512, Level 16, State 1, Procedure dbo.sp_describe_first_result_set, Line 1 [Batch Start Line 0]
The metadata could not be determined because the statement 'SELECT 1' in procedure 'test' is not compatible with the statement 'SELECT 'str'' in procedure 'test'.

This SP can return different datasets depending on a parameter:

ALTER PROCEDURE [dbo].[test]
    @test NVARCHAR(50) = 'test'
AS
BEGIN
    SET NOCOUNT ON;

    IF (@test = 'test')
    BEGIN
        SELECT 1
    END
    ELSE
    BEGIN
        SELECT 'str'
    END
END

Looks like sp_describe_first_result_set does an UNION of the queries because if both the queries returns the same col model, the procedure works.

I’ve even tried to pass @parameter like this:

DECLARE @test NVARCHAR(50) = 'test2'
EXEC [dbo].[sp_describe_first_result_set]
    @tsql = N'test @test = @test'
    ,@params = N'@test NVARCHAR(50)'

but it doesn’t work.

Is there a way to make it work?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

That behavior is expected and is described on the sp_describe_first_result_set doc under the Remarks section:

For each control flow path, the first statement (if any) that returns
a result set is determined by sp_describe_first_result_set.

When multiple possible first statements are found in a batch, their
results can differ in number of columns, column name, nullability, and
data type. How these differences are handled is described in more
detail here:

[…]
  • If the data type differs, an error will be thrown and no result is returned except for the following cases:

    • varchar(a) to varchar(a’) where a’ > a.

    • varchar(a) to varchar(max)

    • nvarchar(a) to nvarchar(a’) where a’ > a.

    • nvarchar(a) to nvarchar(max)

    • varbinary(a) to varbinary(a’) where a’ > a.

    • varbinary(a) to varbinary(max)

The doc has an example where an Error because the data types differ happens, like in your case.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply