Transactions and @@Error function in SQL Server

Question

I just want to check if the place where I put the @@Error and Begin/Commit tran is correct?

I am unsure if I should you the Begin Tran over the DELETE statement instead? And does the @@ERROR make any sense at all?

Thanks!

CREATE PROCEDURE spDeleteAnInactiveEmployee
    @TrainerID int,
    @EActive char (1)
AS
    BEGIN TRY
    BEGIN TRAN

        IF (SELECT COUNT(*) FROM EmployeeDetails ed 
            WHERE TrainerID = @TrainerID) = 0
          RAISERROR ('Trainer details were not deleted. Trainer ID does not exist.', 16, 1)

        IF EXISTS (SELECT * FROM EmployeeDetails ed 
                   WHERE TrainerID = @TrainerID AND EActive = 'Y')
            RAISERROR ('Trainer details were not deleted. Trainer is still active.', 16, 1)

        DELETE FROM [EmployeeDetails]    
        WHERE TrainerID = @TrainerID AND EActive = 'N'

        IF @@ERROR = 0
            COMMIT TRAN

        BEGIN
            PRINT 'Employee ID' + CAST (@TrainerID AS VARCHAR) + ' was successfully deleted.'
        END
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_STATE() AS ErrorState,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;

        IF (XACT_STATE()) = -1
        BEGIN  
            PRINT 'Transaction was not committed' 
            ROLLBACK TRANSACTION;  
        END;  

        IF (XACT_STATE()) = 1
        BEGIN 
            PRINT 'Transaction was committed'
            COMMIT TRANSACTION;
        END;
    END CATCH;
GO

Answer 1

@@ERROR is unnecessary when you use TRY/CATCH. Before TRY/CATCH you had to check @@ERROR after each statement that might fail and use GOTO to force control flow to an error label.

So this should be something like:

CREATE PROCEDURE spDeleteAnInactiveEmployee
    @TrainerID int,
    @EActive char (1)
AS
BEGIN
    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRAN

        IF (SELECT COUNT(*) FROM EmployeeDetails ed 
            WHERE TrainerID = @TrainerID) = 0
          RAISERROR ('Trainer details were not deleted. Trainer ID does not exist.', 16, 1)

        IF EXISTS (SELECT * FROM EmployeeDetails ed 
                   WHERE TrainerID = @TrainerID AND EActive = 'Y')
            RAISERROR ('Trainer details were not deleted. Trainer is still active.', 16, 1)

        DELETE FROM [EmployeeDetails]    
        WHERE TrainerID = @TrainerID AND EActive = 'N'

        COMMIT TRAN
        PRINT 'Employee ID' + CAST (@TrainerID AS VARCHAR) + ' was successfully deleted.'
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK; 
        THROW;
    END CATCH;
END