sql - Dm_exec_describe_first_result_set_for_object 在迭代 ctes 上返回空值

Question

使用：SQL SERVER 2016/2017

我目前正在使用sys.dm_exec_describe_first_result_set_for_object检索大量过程的输出定义。当它查询树导航过程时，我看到了一些意想不到的结果。我已经将问题追溯到内置过程如何识别迭代 cte 中的空对象的问题。

复制问题的代码：

DROP TABLE IF EXISTS dbo.TestTable1;
DROP PROC IF EXISTS dbo.TestTableProc1;
DROP PROC IF EXISTS dbo.TestTableProc2;

CREATE TABLE dbo.TestTable1 (ParentID INT              NOT NULL
,                            ChildID  INT              NOT NULL
,                            Guid     UNIQUEIDENTIFIER NOT NULL);
GO

CREATE PROC dbo.TestTableProc1 (@Value1 INT)
AS
    BEGIN;
        WITH cte AS
            (SELECT T1.ParentID
             ,      T1.ChildID
             ,      T1.Guid
               FROM dbo.TestTable1 T1
              WHERE T1.ParentID = @Value1
             UNION ALL
             SELECT T1.ParentID
             ,      T1.ChildID
             ,      T1.Guid
               FROM dbo.TestTable1 T1)
        SELECT  cte.ParentID
        ,       cte.ChildID
        ,       cte.Guid
          FROM  cte;
    END;
GO

CREATE PROC dbo.TestTableProc2 (@Value1 INT)
AS
    BEGIN;
        WITH cte AS
            (SELECT T1.ParentID
             ,      T1.ChildID
             ,      T1.Guid
               FROM dbo.TestTable1 T1
              WHERE T1.ParentID = @Value1
             UNION ALL
             SELECT cte.ParentID
             ,      cte.ChildID
             ,      cte.Guid
               FROM dbo.TestTable1 T1
               JOIN cte ON T1.ParentID = cte.ChildID)
        SELECT  cte.ParentID
        ,       cte.ChildID
        ,       cte.Guid
          FROM  cte;
    END;
GO

SELECT  'dbo.TestTableProc1'
,       name
,       is_nullable
  FROM  sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.TestTableProc1'), 0)
UNION ALL
SELECT  'dbo.TestTableProc2'
,       name
,       is_nullable
  FROM  sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.TestTableProc2'), 0);

DROP PROC IF EXISTS dbo.TestTableProc1;
DROP PROC IF EXISTS dbo.TestTableProc2;
DROP TABLE IF EXISTS dbo.TestTable1;

这给出了结果集：

正如您从结果中看到的那样，TestTableProc1正确地将所有字段标识为不为空。其中 asTestTableProc2将所有字段标识为可以为空，但它们永远不会。

作为解决方案，我可以将整个过程移到一个子过程中，然后用当前名称调用子过程并添加一个WITH RESULTSET子句来锁定输出。虽然这是一种有效的解决方法，但它意味着性能受到影响，并向数据库中不需要它们的地方添加额外的过程。

我需要的是一种获得正确结果集描述的方法，因为这是一个罕见的调用，我可以在这里对性能进行一些打击，而不是更改主过程本身。

感激地收到任何选项。

标签： sqlsql-serverstored-proceduresiterationcommon-table-expression