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