Adding Error Handling and Transaction to Stored Procedure

Error Handling

 CREATE PROCEDURE [dbo].[uspDoSomething]
 AS
 BEGIN
 
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	SET NOCOUNT ON;
	

	BEGIN TRY

		-- Code Here

		RETURN 0 -- ok
		
	END TRY
	BEGIN CATCH
	
		/********************************************/
        /* Throw Error                              */ 
        /********************************************/
   
		DECLARE
		@iSEVERITY             INT ,            /* User set severity level      */
		@iSTATE                INT ,            /* Parameter in RAISERROR       */
		@vcErrorMsg            VARCHAR(255);    /* Error Message                */
			
        SELECT  @vcErrorMsg = ERROR_PROCEDURE() + 
					N' | SQL Error ' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) +
					N' at line ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '. ' + ERROR_MESSAGE(),
						
                @iSEVERITY  = ERROR_SEVERITY(),
                @iSTATE     = ERROR_STATE()    
            
        
        RAISERROR(@vcErrorMsg,@iSEVERITY,@iSTATE)
		RETURN(-1);
		
	END CATCH;

	
 END
 GO
 

Error Handling with Transaction

CREATE PROCEDURE [dbo].[uspMySp]
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

BEGIN

	BEGIN TRAN

	BEGIN TRY
	
		-- Do something that would fail.

	END TRY

	BEGIN CATCH;

		/********************************************/
		/* Throw Error                              */
		/********************************************/
		DECLARE	@nvcErrorMsg  NVARCHAR(4000),
				@iSeverity    INT,
				@iState       INT;

		SELECT	@nvcErrorMsg =
						ERROR_PROCEDURE() +
						N' | SQL Error ' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) +
						N' at line ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '. ' + ERROR_MESSAGE(),
				@iSeverity	= ERROR_SEVERITY(),
				@iState		= ERROR_STATE();

		RAISERROR(@nvcErrorMsg, @iSeverity, @iState);

		PRINT 'Exception occurred...rolling back changes'

		IF @@TRANCOUNT > 0
			ROLLBACK TRAN;

		IF @@TRANCOUNT = 0
			PRINT 'Successfully rolled back changes...'

		RETURN (-1);

	END CATCH;

	IF @@TRANCOUNT > 0
		COMMIT TRAN

END;