SQL Try catch template by Puru

Do or Do not. There is no try.

Anonymous

I like to keep some templates handy, Many times we have to do some code in production or the code has to have better syntax and checks. So here I give a sample template to execute SQL DML operations in proper syntax. We have used a try-catch block for SQL operation.

/*************************************************************************************************************

	SCRIPT Author : Purusharth Pathak.
	Desc		  :	Perform SQL operations under proper transaction structure and checks
	Date		  : 17-June-2020


*************************************************************************************************************/

SET NOCOUNT ON;
IF EXISTS( _________condition here_____________)
    BEGIN

    	 
    		BEGIN TRANSACTION MyTransactionName;
			BEGIN TRY
				PRINT 'Script BEGIN - What is script doing';
				--Code here. e.g. Delete operation or any other DML
				COMMIT TRANSACTION MyTransactionName;
				PRINT 'Script END, The script is successfully executed';
			END TRY
			BEGIN CATCH
				DECLARE @ErrorMessageOriginal NVARCHAR(4000), @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
				SET @ErrorMessageOriginal = ERROR_MESSAGE();
				SET @ErrorState = ERROR_STATE();
				SET @ErrorSeverity = ERROR_SEVERITY();
				SET @ErrorMessage = 'Error Msg - ' + +ISNULL(@ErrorMessageOriginal, '') + ', Error State-' + CAST(ISNULL(@ErrorState, '') AS NVARCHAR) + ', Error Serverity- ' + CAST(ISNULL(@ErrorSeverity, '') AS NVARCHAR);
				PRINT ' Error when executing script. Rolling back changes!';
				IF @@TRANCOUNT > 0
					ROLLBACK TRANSACTION MyTransactionName;
				RAISERROR(@ErrorMessage, 0, 1) WITH NOWAIT;
			END CATCH;
   

END


SET XACT_ABORT { ON | OFF }  
--When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
--When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction 
--continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. 
--XACT_ABORT OFF is the default setting.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


BEGIN TRANSACTION;  

BEGIN TRY
    PRINT 'Script BEGIN - What is script doing';
    -- code here

END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, 
           ERROR_SEVERITY() AS ErrorSeverity, 
           ERROR_STATE() AS ErrorState, 
           ERROR_PROCEDURE() AS ErrorProcedure, 
           ERROR_LINE() AS ErrorLine, 
           ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO

Thanks for reading, I hope you use such coding practice. Stay tuned for more templates.

Best regards

Puru