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

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?

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.