Error Deploying GraphQL in Fabric: dm_exec_describe_first_result_set

A while ago we suddenly had an error while trying to deploy one Fabric workspace to another using fabric-cicd. The issue was with a GraphQL object and the following error was returned:

Failed to publish GraphQLApi ‘my_graphql’: Operation failed. Error Code: DatasourceInvalidStoredProcedure. Error Message: Only those stored procedures whose metadata for the first result set described by sys.dm_exec_describe_first_result_set are supported.

The GraphQL object indeed uses a couple of custom stored procedures as mutations and apparently one was not behaving. I remembered the sys.dm_exec_describe_first_result_set from my old SSIS days, as it used with stored procs (or other SQL statements) to retrieve the metadata of the columns returned. However, running the system function on the Fabric warehouse just returns that it is not supported:

I wonder how and when the system function is used during deployment. Anyway, let’s take a look at the proc itself. Basically, it had the following format:

CREATE PROC etl.my_custom_sp AS
BEGIN
    BEGIN TRY
        BEGIN TRAN
        --do something
        COMMIT

        SELECT result = 'success';
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        --cleanup

        SELECT
             errorMessage = ERROR_MESSAGE()
            ,result = 'failure';
    END CATCH
END

In the TRY and CATCH block, there are some MERGE and UPDATES, but no result sets are returned. The only result sets are those two SELECT statements. The problem here is these don’t return the same columns. The second SELECT returns additional columns. To fix the issue, we have to return the exact same result set in all possible code branches:

CREATE PROC etl.my_custom_sp AS
BEGIN
    BEGIN TRY
        BEGIN TRAN
        --do something
        COMMIT

        SELECT
             errorMessage = 'N/A'
            ,result = 'success';

    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        --cleanup

        SELECT
             errorMessage = ERROR_MESSAGE()
            ,result = 'failure';
    END CATCH
END

Once the result sets are the same, the GraphQL deployed without issues.