SQL Server 2008 R2嵌套的IF未执行

Question

这已经让我发疯一个多小时了。我确信我忽略了一些愚蠢的东西。请随意嘲笑我，告诉我为什么最后一个"ELSE"在@TransactionType既不是"insert"也不是"update"(它是"delete"，仅供参考(时不执行：

IF @TransactionType = 'insert'
BEGIN
    INSERT INTO [GLOBAL_TEST_INDICATOR_AUDIT](
        [ScheduleID]
        ,[CTSNumber]
        ,[TestNumber]
        ,[GlobalTestIndicator]
        ,[CreatedBy]
        ,[CreatedOn]
        ,[ModifiedBy]
        ,[ModifiedOn] 
        ,[TransactionType] 
        )
    VALUES(
        @ScheduleID,
        @CTSNumber, 
        @TestGroupNumber,
        @GlobalTestIndicator,
        @User,  -- Created By
        GETDATE(),  -- Created On
        NULL,   -- No Modified By for Insert
        NULL,   -- No Modified On for Insert
        @TransactionType
        )
    IF @@ERROR <> 0 RETURN 10 -- Indicates SQL INSERT Operation Failed! 
END 
ELSE 
BEGIN
    IF @TransactionType = 'update'
        BEGIN
            UPDATE [GLOBAL_TEST_INDICATOR_AUDIT]
            SET [ModifiedBy] = @User,
                [ModifiedOn] =  GETDATE(),
                [GlobalTestIndicator] = @GlobalTestIndicator,
                [TransactionType] = @TransactionType
            WHERE [ScheduleID] = @ScheduleID
            IF @@ERROR <> 0 RETURN 20 -- Indicates SQL UPDATE Operation Failed!
        END  
    ELSE
        BEGIN
            UPDATE [GLOBAL_TEST_INDICATOR_AUDIT]
            SET [ModifiedBy] = @User,
                [ModifiedOn] =  GETDATE(),
                [TransactionType] = @TransactionType
            WHERE [ScheduleID] = @ScheduleID
            IF @@ERROR <> 0 RETURN 30 -- Indicates SQL UPDATE Operation Failed!
        END
END

Answer 1

您的倒数第二个UPDATE:

        UPDATE [GLOBAL_TEST_INDICATOR_AUDIT]
        SET [ModifiedBy] = @User,
            [ModifiedOn] =  GETDATE(),
            [GlobalTestIndicator] = @GlobalTestIndicator,
            [TransactionType] = @TransactionType
        WHERE [ScheduleID] = @ScheduleID

几乎与您上一次的UPDATE:相同

        UPDATE [GLOBAL_TEST_INDICATOR_AUDIT]
        SET [ModifiedBy] = @User,
            [ModifiedOn] =  GETDATE(),
            [TransactionType] = @TransactionType
        WHERE [ScheduleID] = @ScheduleID

你怎么能把两者区分开来？

我建议放入一个PRINT语句，看看你的"删除"块是否被触发：

IF @TransactionType = 'update'
    BEGIN
        UPDATE [GLOBAL_TEST_INDICATOR_AUDIT]
        SET [ModifiedBy] = @User,
            [ModifiedOn] =  GETDATE(),
            [GlobalTestIndicator] = @GlobalTestIndicator,
            [TransactionType] = @TransactionType
        WHERE [ScheduleID] = @ScheduleID
        IF @@ERROR <> 0 RETURN 20 -- Indicates SQL UPDATE Operation Failed!
    END  
ELSE
    BEGIN
        PRINT 'Not insert or update'
        UPDATE [GLOBAL_TEST_INDICATOR_AUDIT]
        SET [ModifiedBy] = @User,
            [ModifiedOn] =  GETDATE(),
            [TransactionType] = @TransactionType
        WHERE [ScheduleID] = @ScheduleID
        IF @@ERROR <> 0 RETURN 30 -- Indicates SQL UPDATE Operation Failed!
    END