每当在数据库中插入或更新数据时，我都会尝试更新我的控制表。为此，我创建了一个存储过程，它将获取表名（@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
