将存储过程结果导出到表中

我有以下存储过程，可以根据某些条件生成 SQL 语句。

我希望 SQL 语句也将实际结果返回到表中。

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]

我怎么能这么做呢？

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;

基本上，就在这部分之后：

-- Return validation sql statements --

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

目前，当我执行这个 SP 时，我得到类似的信息：

ReportYearCmd CosCountCmd FranchiseCountCmd  ProductCountCmd

NULL          SELECT DISTINCT [Country Code] AS [COS - Country Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] )        NULL               SELECT DISTINCT [Style Code] AS [Product - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] )  

我想返回实际结果，即如果 Cmd 不为 NULL，如上面的示例所示，则返回 CosCountCmd 和 ProductCountCmd 数据：

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

尝试这个：

/* Execute the dynamic SQL to return their resultsets if cmd variable IS NOT NULL */

IF @ReportYearCmd IS NOT NULL
    EXEC ( @ReportYearCmd );

IF @CosCountCmd IS NOT NULL
    EXEC ( @CosCountCmd );

IF @FranchiseCountCmd IS NOT NULL
    EXEC ( @FranchiseCountCmd );

IF @ProductCountCmd IS NOT NULL
    EXEC ( @ProductCountCmd );

Note:这可能会返回四个不同的结果集，而不是经过透视的结果。

EDIT:

为每个拒绝类别返回一个结果集。

这大约是您无需做大量额外工作即可获得的最接近结果：

DECLARE @Rejects TABLE ( 
    [Report Year] VARCHAR(50),
    [COS - Country Code] VARCHAR(50),
    [Product - Style Code] VARCHAR(50),
    [Franchise - Style Code] VARCHAR(50)
);

IF @ReportYearCmd IS NOT NULL
    INSERT INTO @Rejects ( [Report Year] ) EXEC ( @ReportYearCmd );
    
IF @CosCountCmd IS NOT NULL
    INSERT INTO @Rejects ( [COS - Country Code] ) EXEC ( @CosCountCmd );
    
IF @FranchiseCountCmd IS NOT NULL
    INSERT INTO @Rejects ( [Franchise - Style Code] ) EXEC ( @FranchiseCountCmd );
    
IF @ProductCountCmd IS NOT NULL
    INSERT INTO @Rejects ( [Product - Style Code] ) EXEC ( @ProductCountCmd );

-- Return resultset.
SELECT * FROM @Rejects;

返回类似以下内容：

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

您的调用应用程序在处理结果时需要处理 NULL 值，因为没有简单的方法可以“汇总”NULL 列/行来减少您期望的结果。