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/