Server:嵌套的IF 3级

Question

每当数据被插入或更新到数据库中时，我都试图更新我的控制表。为此，我创建了一个存储过程，它将获取表名(@TableName)、插入的行数(@InsertCount)和更新的行数(@UpdateCount)

我试图实现级别3的嵌套ifs，但是存储过程不起作用。

有人能帮助我理解存储过程的问题吗？

/*
A row is present in the control table   @InsertCount is greater than 0  @UpdateCount is greater than 0  Action
(True/False)    False   False   No action needs to be performed
False   False   True    Insert row in control table and set LastLoadDateTime and DWUpdateDate to current time
False   True    False   Insert row in control table and set LastLoadDateTime and DWInsertDate to current time
False   True    True    Insert row in control table and set LastLoadDateTime, DWUpdateDate, and DWInsertDate to current time
True    False   True    Update row in control table and set LastLoadDateTime and DWUpdateDate to current time
True    True    False   Update row in control table and set LastLoadDateTime and DWInsertDate to current time
True    True    True    Update row in control table and set LastLoadDateTime, DWUpdateDate, and DWInsertDate to current time
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [schema].[StoredProcedure](@TableName nvarchar(50), @InsertCount bigint, @UpdateCount bigint)
as
    DECLARE @JobEndTime datetime;
    DECLARE @IsPresent bit;

    SET @JobEndTime = GETDATE();
    SET @IsPresent = (
        select top 1 COUNT(*)
        from schema.Control_Table
        where TableName = @TableName
    )

    if(@IsPresent = 0)
    BEGIN
        if(@InsertCount <> 0)
        BEGIN
            if(@UpdateCount <> 0)
                insert schema.Control_Table(TableName, LastLoadDateTime, DWInsertDate, DWUpdateDate)
                values (@TableName, @JobEndTime, @JobEndTime, @JobEndTime)
            else
                insert schema.Control_Table(TableName, LastLoadDateTime, DWInsertDate)
                values (@TableName, @JobEndTime, @JobEndTime)
        END
        else
        BEGIN
            if(@UpdateCount <> 0)
                insert schema.Control_Table(TableName, LastLoadDateTime, DWUpdateDate)
                values (@TableName, @JobEndTime, @JobEndTime)
        END
    END
    else
    BEGIN
        if(@InsertCount <> 0)
        BEGIN
            if(@UpdateCount <> 0)
                update schema.Control_Table
                set
                    LastLoadDateTime = @JobEndTime,
                    DWUpdateDate = @JobEndTime,
                    DWInsertDate = @JobEndTime
                where TableName = @TableName
            else
                update schema.Control_Table
                set
                    LastLoadDateTime = @JobEndTime,
                    DWInsertDate = @JobEndTime
                where TableName = @TableName
        END
        else
        BEGIN
            if(@UpdateCount <> 0)
                update schema.Control_Table
                set
                    LastLoadDateTime = @JobEndTime,
                    DWUpdateDate = @JobEndTime
                where TableName = @TableName
        END
    END

Answer 1

可以使用CTE和合并语句简化存储过程，以避免使用嵌套的IF语句。有关工作示例，请参阅此db<>fiddle。

以下是存储过程代码：

CREATE PROCEDURE [sp] 
(
  @TableName VARCHAR(100)
  , @InsertCount BIGINT
  , @UpdateCount BIGINT
)
AS
BEGIN
  DECLARE @JobEndTime DATETIME = GETDATE()
  
  ;WITH InsertRecords AS
  (
    SELECT @TableName AS TableName
      , IIF(@InsertCount = 0, NULL, @JobEndTime) AS DWInsertDate -- NULL if no INSERTs otherwise, GETDATE()
      , IIF(@UpdateCount = 0, NULL, @JobEndTime)  AS DWUpdateDate -- NULL if no UPDATEs otherwise, GETDATE()
      , @JobEndTime AS JobEndTime
  )
  
  MERGE control_table AS target
  USING InsertRecords AS source ON (target.TableName = source.TableName)
  WHEN MATCHED THEN -- Existing record found for table
    UPDATE SET target.LastLoadDateTime = source.JobEndTime
      , target.DWInsertDate = COALESCE(source.DWInsertDate, target.DWInsertDate) -- If no INSERTs, then use existing value in table
      , target.DWUpdateDate = COALESCE(source.DWUpdateDate, target.DWUpdateDate) -- If no UPDATEs, then use existing value in table
  WHEN NOT MATCHED THEN -- No existing record for table
    INSERT (TableName, LastLoadDateTime, DWInsertdate, DWUpdateDate)
    VALUES (source.TableName, source.JobEndTime, source.DWInsertDate, source.DWUpdateDate)
  ;
END

这种工作方式是CTE创建一个伪表，其中包含表名、最后一个加载日期时间、插入日期时间和更新日期时间列的一行。CTE使用一个IIF子句返回GETDATE()如果@InsertCount >0用于insert date列，对于update date列使用@UpdateCount返回相同的值。

然后使用这些数据作为MERGE语句的源，它基本上执行一个UPSERT。如果为提供的表名找到现有行，则更新日期列，如果找不到现有行，则插入新行。

更新现有行的日期列的逻辑如下：

• 如果计数变量为0：
  • 如果控件表中有现有值，则使用现有值，
  • 否则，使用NULL。

• 如果计数大于0：
  • 使用来自CTE (GETDATE())的值。