T-SQL Try-Catch Template

–TRY/CATCH TEMPLATE
BEGIN TRY

–check to see if .Net initiated a SqlTransaction before calling this proc
IF @@TRANCOUNT = 0
BEGIN
–.NET did not initiate an explicit SqlTransaction outside of the procedure call;
— as a result, we initiate an explicit T-SQL transaction here.
BEGIN TRAN;
END

/* PUT SCRIPT CODE RIGHT HERE */

— PRINT ‘>> COMMITING TRANSACTION’ — optional
— no error yet, so commit the transaction
— if query is select only; this statement is meaningless.
IF @@TRANCOUNT = 1 COMMIT TRAN;

END TRY

BEGIN CATCH

/*
There will be either a .Net SqlTransaction or T-SQL transaction here to rollback;
we need to roll back that transaction before handling the exception.

Test XACT_STATE for 0, 1, or -1.
1 = the transaction is COMMITTABLE (we are going to roll back anyways).
-1 = the transaction is UNCOMMITTABLE and should be rolled back.
0 = there is no transaction and a commit or rollback operation would generate an error.

Test whether the transaction is uncommittable.
*/

IF (XACT_STATE() <> 0)
BEGIN
PRINT ‘>> ROLLING BACK TRANSACTION’
ROLLBACK TRANSACTION;
END

—- OPTIONAL EXCEPTION PROPERTIES TO CATCH AS NEEDED.
–@ErrorNo INT = ERROR_NUMBER(),
–@ErrorProcedure NVARCHAR(200) = ERROR_PROCEDURE(),
–@ErrorLine AS INT = ERROR_LINE()

DECLARE @ErrorMessage NVARCHAR(4000) ,
@ErrorSeverity INT ,
@ErrorState INT;

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

— Use RAISERROR inside the CATCH block to return error information
— about the original error that caused execution to jump to the CATCH block.
PRINT ‘>> RAISING ERROR’
https://technet.microsoft.com/en-us/library/ms177497(v=sql.105).aspx
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState );

END CATCH

END CATCH

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s