SQL Server Handle Transaction and Try Catch

SQL Server handles exceptions just like any programming language. Below is a stub to help understand SQL exception handling and you can use in your Procedures as a starting point.

BEGIN TRAN

BEGIN TRY
   
    EXEC P1
    EXEC P2
    COMMIT TRAN

END TRY
BEGIN CATCH
  
    ROLLBACK TRAN
  
    DECLARE
       @ErMessage NVARCHAR(2048),
       @ErSeverity INT,
       @ErState INT
     
     SELECT
       @ErMessage = ERROR_MESSAGE(),
       @ErSeverity = ERROR_SEVERITY(),
       @ErState = ERROR_STATE()
     
     RAISERROR (@ErMessage,
                 @ErSeverity,
                 @ErState )

END CATCH

http://sqlhints.com/2013/06/30/differences-between-raiserror-and-throw-in-sql-server/