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