How do i execute dynamic sql into a created table in stored procedure?

Question

I have the following stored procedure that generates SQL statements based on certain conditions.

I'd like for the SQL statements to also return the actual results into a table.

CREATE TABLE [dbo].[Rejects](
    [Report Year] [varchar](100) NULL,
    [COS - Country Code] [varchar](100) NULL,
    [Franchise - Style Code] [varchar](100) NULL,
    [Product - Style Code] [varchar](100) NULL
) ON [PRIMARY]

How could I do that?

DECLARE
    @ReportYearCmd VARCHAR(1000),
    @CosCountCmd VARCHAR(1000),
    @FranchiseCountCmd AS VARCHAR(1000),
    @ProductCountCmd AS VARCHAR(1000);

WITH Validations AS (
    SELECT TOP 1 * FROM [Handshake] WHERE [Status] = 'Loaded' AND [Update Time] = ( SELECT MAX( [Update Time] ) FROM Handshake )
)
UPDATE Validations 
SET 
    @ReportYearCmd = CASE WHEN Report_Year_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Report Year] AS [Report Year] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Report Year] WHERE [Report Year].[Report Year] = [Fact].[Report Year] );'
    END,
    @CosCountCmd = CASE WHEN COS_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Country Code] AS [COS - Country Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] );' 
    END,
    @FranchiseCountCmd = CASE WHEN  Franchise_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Style Code] AS [Franchise - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Franchise] WHERE [Franchise].[Style Code] = [Fact].[Style Code] );' 
    END,
    @ProductCountCmd = CASE WHEN Product_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Style Code] AS [Product - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] );' 
    END,
    [Status] = CASE
        WHEN ( Report_Year_Count = 0 AND COS_Count = 0 AND Franchise_Count = 0 AND Product_Count = 0 ) THEN 'Good'
        ELSE 'Rejects'
    END
FROM [Validations] 
OUTER APPLY (

    SELECT 
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Report Year] WHERE [Report Year].[Report Year] = [Fact].[Report Year] ) ), 0 ) AS [Report_Year_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] ) ), 0 ) AS [COS_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Franchise] WHERE [Franchise].[Style Code] = [Fact].[Style Code] ) ), 0 ) AS [Franchise_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] ) ), 0 ) AS [Product_Count]

) AS [ValidationCounts];

-- Return validation sql statements --

SELECT
    @ReportYearCmd AS ReportYearCmd,
    @CosCountCmd AS CosCountCmd,
    @FranchiseCountCmd AS FranchiseCountCmd,
    @ProductCountCmd AS ProductCountCmd;

Basically, right after this part:

-- Return validation sql statements --

SELECT
    @ReportYearCmd AS ReportYearCmd,
    @CosCountCmd AS CosCountCmd,
    @FranchiseCountCmd AS FranchiseCountCmd,
    @ProductCountCmd AS ProductCountCmd;

Currently, when I execute this SP I get something like:

ReportYearCmd CosCountCmd                                  FranchiseCountCmd  ProductCountCmd

NULL          SELECT DISTINCT [Country Code] ...        NULL               SELECT DISTINCT [Style Code]...

I'd like to return the actual result, i.e. if the Cmd is NOT NULL, as in the example above, return CosCountCmd and ProductCountCmd data:

Report Year COS - Country Code   Franchise - Style Code   Product - Style Code 
NULL        reject1              NULL                     Rejectxy
NULL        reject2              NULL                     Reject1234
NULL        NULL                 NULL                     Reject567

I have tried the following, but i am getting syntax errors with EXEC

TRUNCATE TABLE [dbo].[Rejects]

INSERT INTO [dbo].[Rejects] (
       [Report Year]
      ,[COS - Country Code]
      ,[Franchise - Style Code]
      ,[Product - Style Code]
)
VALUES (

        EXEC ( @ReportYearCmd );


        EXEC ( @CosCmd );


        EXEC ( @FranchiseCmd );


        EXEC ( @ProductCmd );
)

Answer 1

You may need to create four table variables @ReportYear, @Cos , @Franchise and @tutor with an IDENTITY(1, 1) in them:

DECLARE @ReportYear TABLE (
Id int IDENTITY(1, 1),
ReportYear varchar(4)
);

...

And then execute the dynamic query statements to insert data into the variables:

INSERT INTO @ReportYear (ReportYear)
EXEC ( @ReportYearCmd );

...

Finally you can join those tables based on the IDENTITY column to insert data to the destination table.

Answer 2

Seems like you are making it overly complicated. Why not just run it as:

     INSERT INTO [dbo].[Rejects] (
            [Report Year]
           ,[COS - Country Code]
           ,[Franchise - Style Code]
           ,[Product - Style Code]
     )
     VALUES (CASE WHEN Report_Year_Count = 0 THEN NULL 
                 ELSE (SELECT DISTINCT [Report Year] AS [Report Year] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Report Year] WHERE [Report Year].[Report Year] = [Fact].[Report Year] ))
              END,
         CASE WHEN COS_Count = 0 THEN NULL 
               ELSE (SELECT DISTINCT [Country Code] AS [COS - Country Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] ))
         END,
         CASE WHEN  Franchise_Count = 0 THEN NULL 
             ELSE (SELECT DISTINCT [Style Code] AS [Franchise - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Franchise] WHERE [Franchise].[Style Code] = [Fact].[Style Code] ))
         END,
         CASE WHEN Product_Count = 0 THEN NULL 
             ELSE (SELECT DISTINCT [Style Code] AS [Product - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] ))
         END)