SQL Server – Generic Error Handling I use

This is generic Error Handler I use for my Adhoc Scripts as Well as functions, stored procedures,etc.

-- Adding the error message.
USE master;
GO
EXEC sp_addmessage 50001, 16, 
   N'The Record doesn''t exist. Parameter : %s ,  Value : %s',@replace='Replace';
GO

-- Standard Variables Declarations

  DECLARE
	@ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200),
	@ErrParameter    NVARCHAR(255),
	@ErrParameterValue NVARCHAR(255)

-- Stored Procedure Specific Variables Declarations
 DECLARE 
		@pUserName NVARCHAR(50)

 SELECT  
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');


SELECT @pUserName = username
		 FROM ps.User_ WITH (NOLOCK)
		WHERE UserID = @pUserID
		AND IsActive = 1 

IF @pUserName IS NULL
BEGIN
SELECT  @ErrorNumber = 50001 ,
        @ErrorSeverity = 16 ,
        @ErrorState    = 1 ,
	    @ErrorLine = ERROR_LINE(),
		@ErrParameter   = '@pUserID',
		@ErrParameterValue = CONVERT(VARCHAR(50) , @pUserID) ,
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

GOTO ErrorHandler
END



GOTO ExitScript


ErrorHandler:


    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
 RAISERROR 
        (
        @ErrorNumber, 
		@ErrorSeverity,   
        @ErrorState,     -- parameter: original error state.
		@ErrParameter,   -- '@pUserID',
		@ErrParameterValue
        );


ExitScript:

END

Common Error Handling Stored Procedure

-- Check to see whether this stored procedure exists.  
IF OBJECT_ID (N'common.GetErrorInfo', N'P') IS NOT NULL  
    DROP PROCEDURE common.GetErrorInfo;  
GO  
  
-- Create procedure to retrieve error information.  
CREATE PROCEDURE common.GetErrorInfo  
AS  
    SELECT   
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_LINE () AS ErrorLine  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  
GO  


EXEC common.GetErrorInfo
  
-- SET XACT_ABORT ON will cause the transaction to be uncommittable  
-- when the constraint violation occurs.   
SET XACT_ABORT ON;  
  
BEGIN TRY  
    BEGIN TRANSACTION;  
        -- statement will generate a error.  
        SELECT 1/0 AS [1/0 Error]
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    EXECUTE common.GetErrorInfo;  
  
    -- Test XACT_STATE:  
        -- If 1, the transaction is committable.  
        -- If -1, the transaction is uncommittable and should   
        --     be rolled back.  
        -- XACT_STATE = 0 means that there is no transaction and  
        --     a commit or rollback operation would generate an error.  
  
    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  
  
    -- Test whether the transaction is committable.  
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;  
GO