sql-server - 如何创建检查状态、验证然后更新状态的存储过程？

问题描述

我有以下“握手”表：

CREATE TABLE [dbo].[Handshake]
(
    [Report Year] [varchar](100) NULL,
    [Status] [varchar](100) NULL,
    [Update Time] [datetime] NULL,
    [Process Time] [datetime] NULL
) ON [PRIMARY]
GO

这里的一些数据：

Report Year Status  Update Time             Process Time
--------------------------------------------------------
2020 1+2    Loaded  2020-08-25 13:10:19.073 NULL
2020 6+8    Loaded  2020-08-25 13:11:30.733 NULL
2020 1+2    Loaded  2020-08-25 13:11:47.257 NULL

我有另一个表，“事实”，用于每次插入上述“报告年”的负载，并且当后端进程开始从“WIP”加载/更新状态时，一条记录被插入到握手表中从上面的示例握手数据中可以看出，加载完成后“加载”为“加载”。

在状态为之后Loaded，我每次都手动进入并针对表运行以下查询Fact以针对其他表验证数据。

SELECT COUNT(*) AS 'Report Year Count' 
FROM Fact 
WHERE [Report Year] NOT IN (SELECT DISTINCT [Report Year] FROM [Report Year])

SELECT COUNT(*) AS 'COS Count' 
FROM Fact 
WHERE [Country Code] NOT IN (SELECT DISTINCT [Country Code] FROM [COS])

SELECT COUNT(*) AS 'Franchise Count' 
FROM Fact 
WHERE [Style Code] NOT IN (SELECT DISTINCT [Style Code] FROM [Franchise])

SELECT COUNT(*) AS 'Product Count' 
FROM Fact 
WHERE [Style Code] NOT IN (SELECT DISTINCT [Style Code] FROM [Product])

如果上述查询的计数为 0，那么我不会检查这些表的拒绝是什么。
如果计数 > 0，我会运行类似的查询来识别拒绝。

例如，如果 Franchise 和 Product 返回计数 1063，

然后我运行以下相关查询来识别这些表中丢失的拒绝：

SELECT DISTINCT [Style Code] AS 'Franchise - Style Code' 
FROM Fact 
WHERE [Style Code] NOT IN (SELECT DISTINCT [Style Code] FROM [Franchise])

SELECT DISTINCT [Style Code] AS 'Product - Style Code' 
FROM Fact 
WHERE [Style Code] NOT IN (SELECT DISTINCT [Style Code] FROM [Product])

如果有“拒绝”（不一致的数据），我会向相关用户发送一封电子邮件，说明拒绝的位置，以便他们可以更新表格（即特许经营权、COS、产品、报告年份）以在我继续之前包含丢失的拒绝下一步（处理数据库）。

我不想连续手动执行此验证任务，而是想编写一个存储过程，该过程将由某种形式的脚本/作业（目前与此线程的范围无关）触发，它将检查握手表中的最新“ Loaded”状态，运行验证查询，如果有拒绝，则从“Loaded”->“Rejects”更新 Handshake 表中该记录的状态。否则，如果没有拒绝，则将状态更新为“良好”以表示没有发现拒绝。

如果有拒绝，我想将结果保存到标量变量（即 cmd）中，以便稍后将它们提取到各种 csv 文件中

这是我的伪代码：

Create PROCEDURE [dbo].[CheckRejects] AS
    DECLARE @cmd NVARCHAR(1000)
BEGIN
    --check Handshake Status. 
    IF Status='Loaded' THEN --RUN Validation Queries
        SELECT COUNT(*) as 'Report Year Count' FROM Fact WHERE [Report Year] NOT IN ( SELECT DISTINCT [Report Year] FROM [Report Year] )
        SELECT COUNT(*) as 'COS Count' FROM Fact WHERE [Country Code] NOT IN ( SELECT DISTINCT [Country Code] FROM [COS] )
        SELECT COUNT(*) as 'Franchise Count' FROM Fact WHERE [Style Code] NOT IN ( SELECT DISTINCT [Style Code] FROM [Franchise] )
        SELECT COUNT(*) as 'Product Count' FROM Fact WHERE [Style Code] NOT IN ( SELECT DISTINCT [Style Code] FROM [Product] )
        
        case 
            when 'Report Year Count' > 0 THEN
                SET Status='Rejects'
                SET @cmd = SELECT DISTINCT [Report Year] as 'Report Year' FROM Fact WHERE [Report Year] NOT IN ( SELECT DISTINCT [Report Year] FROM [Report Year] )
            when 'COS Count' > 0 THEN 
                SET Status='Rejects'
                SET @cmd = SELECT DISTINCT [Country Code] as 'COS - Country Code' FROM Fact WHERE [Country Code] NOT IN ( SELECT DISTINCT [Country Code] FROM [COS] )
            when 'Franchise Count' > 0 THEN
                SET Status='Rejects'
                SET @cmd = SELECT DISTINCT [Style Code] as 'Franchise - Style Code' FROM Fact WHERE [Style Code] NOT IN ( SELECT DISTINCT [Style Code] FROM [Franchise] )
            when 'Product Count' > 0 THEN
                SET Status='Rejects'
                SET @cmd = SELECT DISTINCT [Style Code] as 'Product - Style Code' FROM Fact WHERE [Style Code] NOT IN ( SELECT DISTINCT [Style Code] FROM [Product] )
            ELSE SET Status='Good' -- No Rejects, continue to processing DB
END
SELECT @cmd As Rejects

标签： sql-server

解决方案

我无权访问您的数据/架构，因此这是未经测试的尝试，但您可以尝试以下操作：

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

SET NOCOUNT ON;

-- Handshake Validations --
SELECT TOP 1
    @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,
    @ValidationStatus = CASE
        WHEN ( Report_Year_Count = 0 AND COS_Count = 0 AND Franchise_Count = 0 AND Product_Count = 0 ) THEN 'Good'
        ELSE 'Rejects'
    END
FROM [Handshake] 
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]
WHERE 
    [Handshake].[Status] = 'Loaded'
ORDER BY
    [Handshake].[Update Time] DESC;
        
-- Return validation results --

SELECT
    ISNULL( @ValidationStatus, 'Good' ) AS ValidationStatus,
    @ReportYearCmd AS ReportYearCmd,
    @CosCountCmd AS CosCountCmd,
    @FranchiseCountCmd AS FranchiseCountCmd,
    @ProductCountCmd AS ProductCountCmd;

更新：

如果我想使用 ValidationStatus 作为存储过程的一部分来更新握手表，我在哪里可以做到这一点？

尝试以下操作：

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 results --

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

-- Optionally email the dynamic statements to someone --

IF @ReportYearCmd IS NOT NULL
    EXEC msdb..sp_send_dbmail 
        @profile_name = 'your_dbmail_profile',
        @recipients = 'email@domain.com',
        @subject = 'ReportYear Load Rejects',
        @execute_query_database = 'your_db_name',
        @query = @ReportYearCmd,
        @query_result_header = 0,
        @query_attachment_filename = 'ReportYearRejects.csv',
        @attach_query_result_as_file = 1,
        @query_result_separator = ';',
        @exclude_query_output = 1,
        @query_result_no_padding = 1;

IF @CosCountCmd IS NOT NULL
    EXEC msdb..sp_send_dbmail 
        @profile_name = 'your_dbmail_profile',
        @recipients = 'email@domain.com',
        @subject = 'COS Load Rejects',
        @execute_query_database = 'your_db_name',
        @query = @CosCountCmd,
        @query_result_header = 0,
        @query_attachment_filename = 'COSRejects.csv',
        @attach_query_result_as_file = 1,
        @query_result_separator = ';',
        @exclude_query_output = 1,
        @query_result_no_padding = 1;

IF @FranchiseCountCmd IS NOT NULL
    EXEC msdb..sp_send_dbmail 
        @profile_name = 'your_dbmail_profile',
        @recipients = 'email@domain.com',
        @subject = 'Franchise Load Rejects',
        @execute_query_database = 'your_db_name',
        @query = @FranchiseCountCmd,
        @query_result_header = 0,
        @query_attachment_filename = 'FranchiseRejects.csv',
        @attach_query_result_as_file = 1,
        @query_result_separator = ';',
        @exclude_query_output = 1,
        @query_result_no_padding = 1;

IF @ProductCountCmd IS NOT NULL
    EXEC msdb..sp_send_dbmail 
        @profile_name = 'your_dbmail_profile',
        @recipients = 'email@domain.com',
        @subject = 'Product Load Rejects',
        @execute_query_database = 'your_db_name',
        @query = @ProductCountCmd,
        @query_result_header = 0,
        @query_attachment_filename = 'ProductRejects.csv',
        @attach_query_result_as_file = 1,
        @query_result_separator = ';',
        @exclude_query_output = 1,
        @query_result_no_padding = 1;

sql-server - 如何创建检查状态、验证然后更新状态的存储过程？

问题描述

解决方案

推荐阅读