SQL Server - Stored Procedure With Transaction

SP_Transaction.sql

CREATE PROCEDURE [Schema].[SP_Name] (@Param [DataType(Size)] = DEFAULT_VALUE)
AS
BEGIN TRY
  BEGIN TRANSACTION

  -- Business Logic Here
  
  -- Throw Exception Example
  -- RAISERROR (N'Please delete detail vital sign first !!' , 11, 1);
  -- RETURN; 

COMMIT TRANSACTION
END TRY

BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  SELECT
    @ErrorMessage = ERROR_MESSAGE()
   ,@ErrorSeverity = ERROR_SEVERITY()
   ,@ErrorState = ERROR_STATE();

  IF XACT_STATE() <> 0 BEGIN
    ROLLBACK TRANSACTION
  END

  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
  
END CATCH
GO