mardi 9 juin 2015

SQL Server 2008 R2 Nested IFs Not Executing

This has been driving me crazy for over an hour. I'm sure I'm overlooking something idiotic. Feel free to mock me, just tell me why the last "ELSE" doesn't execute when @TransactionType is neither 'insert' nor 'update' (it's 'delete', just FYI):

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

Aucun commentaire:

Enregistrer un commentaire