How to Handle/Log Exception Occur in Stored Procedure?

-- =============================================

-- Author:  <Suneel Kumar> 

-- Create date: <Create Date,17 July 2017> 

-- Description: <Manage Employee Details> 

-- =============================================

CREATE PROC [DBO].[usp_ManageEmployee]       

(                                                       

       @EmpCode        VARCHAR(100)=NULL,

       @EmpName        VARCHAR(100)=NULL,

       @Mobile         VARCHAR(100)=NULL,

       @EmailId        VARCHAR(100)=NULL,

       @EmpType        VARCHAR(100)=NULL,

       @IsActive       BIT=NULL,

       @salary         MONEY=NULL,                                                                      

       @MESSAGEOUT     VARCHAR(MAX)='' OUTPUT

)                                                                     

AS                                                         

BEGIN 

SET NOCOUNT ON;

BEGIN TRY

   INSERT INTO tblEmployee (EmpCode,EmpName,Mobile,Salary,EmailId,EmpType,IsActive)

    VALUES(@EmpCode,@EmpName,@Mobile,@Salary,@EmailId,@EmpType,@IsActive)

END TRY

BEGIN CATCH       

  SELECT @MESSAGEOUT= 'PROCNAME: usp_ManageEmployee ERRORSTATE :' + CAST(ERROR_STATE() AS VARCHAR)

                                                  + '  ERRORNUMBER :'+ CAST(ERROR_NUMBER() AS VARCHAR)

                                                  + '  ERRORLINE:'+ CAST(ERROR_LINE() AS VARCHAR)

                                                  + '  ERROR_MSG  '+ ERROR_MESSAGE()

   EXECUTE DBO.usp_LogError @PROCNAME='uspQUICKSEARCHBOOKINGS'

END CATCH

END 

GO

-- =============================================  

-- Author:  <Suneel Kumar>  

-- Create date: <Create Date,17 July 2017>  

-- Description: <Description,Insert,Error log in ErrorLog Table,>  

-- =============================================  

 

CREATE PROCEDURE [dbo].[usp_LogError]  

 @ProcName varchar(100)='', --Procedure name in which error is occured  

 @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted  

AS                               -- by uspLogError in the ErrorLog table  

BEGIN  

    SET NOCOUNT ON;  

    -- Output parameter value of 0 indicates that error  

    -- information was not logged  

    SET @ErrorLogID = 0;  

 

    BEGIN TRY  

        -- Return if there is no error information to log  

        IF ERROR_NUMBER() IS NULL  

            RETURN;  

        -- Return if inside an uncommittable transaction.  

        -- Data insertion/modification is not allowed when  

        -- a transaction is in an uncommittable state.  

        IF XACT_STATE() = -1  

        BEGIN  

            PRINT 'Cannot log error since the current transaction is in an uncommittable state. '  

                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';  

            RETURN;  

        END  

 

        INSERT [dbo].[ErrorLog]  

            (  

            [ErrorNumber],  

            [ErrorSeverity],  

            [ErrorState],  

            [ErrorProcedure],  

            [ErrorLine],  

            [ErrorMessage] ,

             ErrorTime

            )  

        VALUES  

            (            

            ERROR_NUMBER(),  

            ERROR_SEVERITY(),  

            ERROR_STATE(),  

            @ProcName,            

            ERROR_LINE(),  

            ERROR_MESSAGE()

            ,GETDATE()

            );  

 

        -- Pass back the ErrorLogID of the row inserted  

        SET @ErrorLogID = @@IDENTITY;  

    END TRY  

    BEGIN CATCH  

        PRINT 'An error occurred in stored procedure uspLogError: ';  

        EXECUTE [dbo].[usp_PrintError];  

        RETURN -1;  

    END CATCH  

END;

GO

-- =============================================

-- Author:  <Suneel Gupta>

-- Create date: <Create Date,17 July 2017,>

-- Description: <Description,if any error throw sp then print Error>

-- =============================================

create PROCEDURE [dbo].[usp_PrintError]

AS

BEGIN

    SET NOCOUNT ON;

    -- Print error information.

    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +

          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +

          ', State ' + CONVERT(varchar(5), ERROR_STATE()) +

          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +

          ', Line ' + CONVERT(varchar(5), ERROR_LINE());

    PRINT ERROR_MESSAGE();

END;