SQL Server – Concatenation of Dynamic SQL Commands with UNION Returns Inconsistent Results

I'm testing a report that I wrote in T-SQL and I'm having problems with the results returned. Here is the section of the code that I have a problem with;

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

SELECT @TSQL AS SQLCommand
IN #Order

INSERT INTO #Tables
select schema_name (t.schema_id) as TenantId,
t.[name] TaskTable,
t2.[name] RecordTable,
c.[name] RecordColumn
of sys.tables t, sys.tables t2
join sys.columns c on t2.object_id = c.object_id
where t.[name] in (& # 39; Task & # 39; Tasks & # 39;)
and t2.[name] in (& # 39; TimeRecord & # 39 ;, & # 39; TimeRecords & # 39;)
and schema_name (t.schema_id)> = 50
and schema_name (t.schema_id) <> 275 - Excludes this because tables are missing
and C.[name] like & # 39; FK_Task% & # 39;
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
of sys.tables t, sys.tables t2
join sys.columns c on t2.object_id = c.object_id
where t.[name] in (& # 39; Tasks_NEW & # 39 ;, & # 39; Task_NEW & # 39;)
and t2.[name] in (& # 39; TimeRecord_NEW & # 39; TimeRecords_NEW & # 39;)
and schema_name (t.schema_id)> = 50
and C.[name] like & # 39; FK_Task% & # 39;
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
of sys.tables t, sys.tables t2
join sys.columns c on t2.object_id = c.object_id
where t.[name] in (& # 39; Tasks_WOM & # 39 ;, & # 39; Task_WOM & # 39;)
and t2.[name] in (& # 39; TimeRecord_WOM & # 39; TimeRecords_WOM & # 39;)
and schema_name (t.schema_id)> = 50
and C.[name] like & # 39; FK_Task% & # 39;
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)

DURING @Iter <= @MaxRownum
TO START
SELECT @TenantId = TenantId, @TaskTable = TaskTable, @RecordTable = RecordTable, @RecordColumn = RecordColumn
FROM #Tables
O id = @Iter

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

INSERT INTO #Commands
SELECT @TSQL AS SQLCommand

SET @Iter = @Iter + 1

END

SELECT @TSQLFinal = IsNull (@TSQLFinal + & # 39;
UNION
& # 39; & # 39; & # 39;) + SQLCommand
FROM #Commands

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

If I SELECT * FROM #Commands I can see all the commands generated by my dynamic statement, which is 275 lines in this case.

It's perfect, but if I can SELECT * FROM # results I do not receive 275 lines. I sometimes have 30 lines, sometimes 100, but this is never consistent.

What I'm waiting for here is that all my commands from the #Commands temporary table are executed and the results of these commands are placed in the #results table (where UNION).

Can anything detect what could go wrong here?

Thanks in advance!

To M.