使用 UNION 连接动态 SQL 命令返回不一致的结果

我正在测试我用 T-SQL 编写的报告，但返回的结果存在一些问题。这是我遇到问题的代码部分；

CREATE TABLE #Tables
    (   ID      INT IDENTITY (1,1),
        TenantId        nvarchar(10),
        TaskTable   nvarchar(50),
        RecordTable nvarchar(50),   
        RecordColumn    nvarchar(50)    )

SELECT @TSQL AS SQLCommand
    INTO #Commands

INSERT INTO #Tables
    select schema_name(t.schema_id) as TenantId, 
                t.[name] TaskTable,
                t2.[name] RecordTable,
                c.[name] RecordColumn
    from sys.tables t, sys.tables t2
    join sys.columns c on t2.object_id = c.object_id
    where t.[name] in ('Task', 'Tasks')
    and t2.[name] in ('TimeRecord', 'TimeRecords')
    and schema_name(t.schema_id) >= 50
    and schema_name(t.schema_id) <> 275     -- Exclude this because it's missing tables
    and c.[name] like 'FK_Task%'
    group by (schema_name(t.schema_id)), t.[name], t2.[name], c.[name]

        union 

    select schema_name(t.schema_id) as TenantId, 
                t.[name] TaskTable,
                t2.[name] RecordTable,
                c.[name] RecordColumn
    from sys.tables t, sys.tables t2
    join sys.columns c on t2.object_id = c.object_id
    where t.[name] in ('Tasks_NEW', 'Task_NEW')
    and t2.[name] in ('TimeRecord_NEW', 'TimeRecords_NEW')
    and schema_name(t.schema_id) >= 50
    and c.[name] like 'FK_Task%'
    group by (schema_name(t.schema_id)), t.[name], t2.[name], c.[name]

        union

    select schema_name(t.schema_id) as TenantId, 
                t.[name] TaskTable,
                t2.[name] RecordTable,
                c.[name] RecordColumn
    from sys.tables t, sys.tables t2
    join sys.columns c on t2.object_id = c.object_id
    where t.[name] in ('Tasks_WOM', 'Task_WOM')
    and t2.[name] in ('TimeRecord_WOM', 'TimeRecords_WOM')
    and schema_name(t.schema_id) >= 50
    and c.[name] like 'FK_Task%'
    group by (schema_name(t.schema_id)), t.[name], t2.[name], c.[name]


SET @MaxRownum = (SELECT MAX(id) FROM #Tables)
SET @Iter = (SELECT MIN(id) FROM #Tables)

WHILE @Iter <= @MaxRownum
BEGIN
     SELECT @TenantId = TenantId, @TaskTable = TaskTable, @RecordTable = RecordTable, @RecordColumn = RecordColumn
        FROM #Tables
             WHERE id = @Iter

SET @TSQL =     N'SELECT '+@TenantId+' AS TenantId, COUNT(*) AS IntegrationDates, (SELECT COUNT(*) FROM [Dynamic].['+@TenantId+'].['+@TaskTable+']  WHERE SystemId NOT IN (SELECT '+@RecordColumn+' FROM [Dynamic].['+@TenantId+'].['+@RecordTable+'])) AS MissingTimeRecords FROM [Dynamic].['+@TenantId+'].['+@TaskTable+']  WHERE IntegrationDate IS NULL'

INSERT INTO #Commands
SELECT @TSQL AS SQLCommand  

SET @Iter = @Iter + 1

END

SELECT @TSQLFinal = IsNull(@TSQLFinal + '
UNION 
', '' ) + SQLCommand
FROM #Commands

CREATE TABLE #Results   (   ApplicationId INT,
                                                IntegrationDates INT,
                                                MissingTimeRecords INT)
INSERT INTO #results
EXEC (@TSQLFinal)

如果我SELECT * FROM #Commands看到我的动态语句构建的所有命令，在本例中为 275 行。

这是完美的，但如果我SELECT * FROM #results没有得到 275 行。我有时会得到 30 行，有时会得到 100 行，但它从来都不一致。

我在这里期待的是#Commands 临时表中的所有命令都得到执行，并且这些命令的结果被放入#results 表（因此是 UNION）

有什么能发现这里可能出了什么问题吗？

提前致谢！

汤姆。