Is it necessary to put transactions in this code?

Viewed 119

my supervisor asked me to not put transactions and commit etc in this code because he says that it's useless to put transactions in this procedure. He's well experienced and i can't directly argue with him so need your views on it ?

ALTER PROCEDURE [Employee].[usp_InsertEmployeeAdvances](
      @AdvanceID        BIGINT,
      @Employee_ID      INT,
      @AdvanceDate      DATETIME,
      @Amount           MONEY,
      @MonthlyDeduction MONEY,
      @Balance          MONEY,
      @SYSTEMUSER_ID    INT,
      @EntryDateTime    DATETIME = NULL,
      @ProcedureType    SMALLINT)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION [Trans1]
        IF EXISTS
           (
              SELECT *
              FROM Employee.Advance
              WHERE AdvanceID = @AdvanceID
           )
        BEGIN
            --UPDATION OF THE RECORD
            IF @ProcedureType = 1
            BEGIN
                SET @Amount = @Amount * -1;
            END
            UPDATE Employee.Advance
            SET
                  Employee_ID = @Employee_ID,
                  AdvanceDate = @AdvanceDate,
                  Amount = @Amount,
                  MonthlyDeduction = @MonthlyDeduction,
                  Balance = @Balance,
                  EntryDateTime = GETDATE()
            WHERE AdvanceID = @AdvanceID
        END
        ELSE
        BEGIN
            DECLARE @LastRecordID INT
            DECLARE @LastBalance MONEY
            SET @LastRecordID =
               (
                  SELECT MAX(EA.AdvanceID)
                  FROM Employee.Advance EA
                  WHERE EA.Employee_ID = @Employee_ID
               )
            SET @LastBalance =
               (
                  SELECT EA.Balance
                  FROM Employee.Advance EA
                  WHERE EA.AdvanceID = ISNULL(@LastRecordID, 0)
               )
            IF(@ProcedureType = 0) --Advances
            BEGIN
                SET @Balance = ISNULL(@LastBalance, 0) + @Amount
                INSERT INTO Employee.Advance
                                            (Employee_ID,
                                             AdvanceDate,
                                             Amount,
                                             MonthlyDeduction,
                                             Balance,
                                             User_ID,
                                             EntryDateTime
                                            )
                VALUES
                       (@Employee_ID,
                        @AdvanceDate,
                        @Amount,
                        @MonthlyDeduction,
                        @Balance,
                        @SYSTEMUSER_ID,
                        GETDATE())
            END
            ELSE --Receivings
            BEGIN
                IF NOT EXISTS
                   (
                      SELECT *
                      FROM Employee.Advance EA
                      WHERE EA.Employee_ID = @Employee_ID
                            AND EA.Balance > 0
                            AND EA.AdvanceID =
                         (
                            SELECT MAX(AdvanceID)
                            FROM Advance
                            WHERE Employee_ID = @Employee_ID
                         )
                   )
                BEGIN
                    RAISERROR('This Employee has no advances history', 16, 1)
                    RETURN
                    --Select 0                  
                END
                ELSE
                BEGIN
                    SET @Balance = ISNULL(@LastBalance, 0) - @Amount
                    INSERT INTO Employee.Advance
                                                (Employee_ID,
                                                 AdvanceDate,
                                                 Amount,
                                                 MonthlyDeduction,
                                                 Balance,
                                                 User_ID,
                                                 EntryDateTime
                                                )
                    VALUES
                           (@Employee_ID,
                            @AdvanceDate,
                            -1 * @Amount,
                            @MonthlyDeduction,
                            @Balance,
                            @SYSTEMUSER_ID,
                            GETDATE())
                END
            END
        END
        COMMIT TRANSACTION [Trans1]
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION [Trans1]
    END CATCH
END
4 Answers
Related