使用临时表时 SQL Server 描述第一个结果集失败 (sp_describe_first_result_set)

Question

长话短说，我有一个第三方应用程序，当它无法检索查询/存储过程的元数据时，它的行为会有所不同。

众所周知，sys.sp_describe_first_result_set当存储过程中使用临时表时，无法检索该存储过程的元数据。

为了简单起见，这里是一个简单的例子。

CREATE PROCEDURE dbo.Test
    @Seed INT = 0
AS
BEGIN
    CREATE TABLE #MyTemp (
        ID INT NOT NULL
    );

    INSERT INTO #MyTemp (ID)
    VALUES
          (@Seed + 1)
        , (@Seed + 2)
        , (@Seed + 3)
    ;

    SELECT
        ID
    FROM
        #MyTemp
END

执行此 SP 将返回一个结果集，其中有一列 ( ID ) 和三条记录。

EXEC dbo.Test
    @Seed = 1;

结果是:

ID
-----------
2
3
4

但是，尝试获取元数据将会失败:

EXEC sys.sp_describe_first_result_set @tsql = N'EXEC dbo.Test @Seed = 1;';

结果是:

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 24]
The metadata could not be determined because statement 'INSERT INTO #MyTemp (ID)
    VALUES
          (@Seed + 1)
        , (@Seed + 2)
        , (@Seed + 3)' in procedure 'Test' uses a temp table.

(这是预期的，因为这是 sp_describe_first_result_set 的已知限制)

问题是，当我们的第三方应用程序遇到此错误时，它会执行两次 SP(首先分析结果集并创建临时表，然后执行 INSERT .. EXEC 将数据加载到临时表中它创建了)。

当元数据可用时，它将使用 sys.sp_describe_first_result_set 获取元数据并使用该信息创建临时表。

由于我们没有任何低于 2012 版本的 SQL Server，我可以使用 WITH RESULT SETS子句，但是不可能在所述应用程序中对其进行配置，或手动提供元数据信息。

如何使元数据可供使用此 SP 的应用程序使用？

我在回答中提供了两种解决方案，但我很好奇是否有一个我不知道。

Answer 1

我当前的解决方案是创建一个包装存储过程，它执行现有的存储过程，传递所有参数，但定义结果集的元数据。

继续问题中的示例:

EXEC sp_rename 'dbo.Test', 'Test_Logic', 'OBJECT';
GO

CREATE PROCEDURE dbo.Test
    @Seed INT = 0
AS
BEGIN
    EXEC dbo.Test_Logic
        @Seed = @Seed
    WITH RESULT SETS (
        (
            ID INT
        )
    )
    ;
END

现在，如果我尝试获取元数据，我可以获得它:

EXEC sys.sp_describe_first_result_set @tsql = N'EXEC dbo.Test @Seed = 1;';

结果是:

is_hidden column_ordinal name                                                                                                                             is_nullable system_type_id system_type_name                                                                                                                 max_length precision scale collation_name                                                                                                                   user_type_id user_type_database                                                                                                               user_type_schema                                                                                                                 user_type_name                                                                                                                   assembly_qualified_type_name                                                                                                                                                                                                                                     xml_collection_id xml_collection_database                                                                                                          xml_collection_schema                                                                                                            xml_collection_name                                                                                                              is_xml_document is_case_sensitive is_fixed_length_clr_type source_server                                                                                                                    source_database                                                                                                                  source_schema                                                                                                                    source_table                                                                                                                     source_column                                                                                                                    is_identity_column is_part_of_unique_key is_updateable is_computed_column is_sparse_column_set ordinal_in_order_by_list order_by_is_descending order_by_list_length tds_type_id tds_length  tds_collation_id tds_collation_sort_id
--------- -------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- --------- ----- -------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------- ----------------- ------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ --------------------- ------------- ------------------ -------------------- ------------------------ ---------------------- -------------------- ----------- ----------- ---------------- ---------------------
0         1              ID                                                                                                                               1           56             int                                                                                                                              4          10        0     NULL                                                                                                                             NULL         NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL              NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                             0               0                 0                        NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                             NULL               NULL                  NULL          NULL               NULL                 NULL                     NULL                   NULL                 38          4           NULL             NULL

(这里看起来很糟糕，但它有效)

缺点是现在我必须维护两个SP，并且还有后续的SP执行。

替代解决方案

另一种解决方案是使用动态 sql 和 sys.sp_executesql 来通过 WITH RESULT SETS 子句运行它。

这样做的主要缺点是，SQL Server 和 VisualStudio(数据库项目)无法跟踪动态 SQL 中的依赖关系。

这看起来像这样:

ALTER PROCEDURE dbo.Test
    @Seed INT = 0
AS
BEGIN
    CREATE TABLE #MyTemp (
        ID INT NOT NULL
    );

    INSERT INTO #MyTemp (ID)
    VALUES
          (@Seed + 1)
        , (@Seed + 2)
        , (@Seed + 3)
    ;


    DECLARE @STMT NVARCHAR(MAX) = N'
SELECT
    ID
FROM
    #MyTemp
;';

    EXEC sys.sp_executesql
        @stmt = @STMT
    WITH RESULT SETS (
        (
            ID INT
        )
    )
END

当然，我认为SP越复杂，动态SQL就越难维护，所以我更喜欢第一种方案。