Q:

error handling in stored procedure in case of wrong input format

I am trying to log the error caused by the wrong input data format eg. input of alphabets when there is numeric field etc. 
But somehow I cannot log that error, I cannot understand where I am doing mistake. Please can anyone help me.
SAMPLE PROCEDURE
ALTER PROCEDURE [dbo].[p_set_OPD_Registration] (
    @name_pr VARCHAR(120)
    ,@age NUMERIC(20, 4)
    ,@v_out_result_num INT OUTPUT
    ,@v_out_result_msg VARCHAR(1000) OUTPUT
    )
AS
BEGIN
    BEGIN TRY
        INSERT INTO [dbo].[tbl_patient_registration] (
            [patient_id_pr]
            ,[name_pr]
            ,[age]
            ,[patient_address_1]
            )
        VALUES (
            '2937'
            ,@name_pr
            ,ISNUMERIC(@age)
            ,'test'
            )

        IF @@ROWCOUNT > 0
        BEGIN
            SET @v_out_result_num = 1;
        END
        ELSE
        BEGIN
            SET @v_out_result_num = 2;
        END
    END TRY

    BEGIN CATCH
        INSERT INTO [dbo].[z_sql_exceptional_error]
        VALUES (
            ERROR_NUMBER()
            ,ERROR_SEVERITY()
            ,ERROR_STATE()
            ,ERROR_PROCEDURE()
            ,ERROR_LINE()
            ,ERROR_MESSAGE()
            ,getdate()
            ,'person ID'
            );

        SET @v_out_result_num = 0;
        SET @v_out_result_msg = SUBSTRING(error_message(), 1, 500);
    END CATCH
END

When I do something wrong with the table, the error is logged in the exceptional table properly like:
ErrorID ErrorNumber ErrorSeverity   ErrorState  ErrorProcedure  ErrorLine   ErrorMessage    ErrorTime   ErrorDescription
1   8152    16  14  p_set_OPD_Registration  15  String or binary data would be truncated.   2016-08-03 19:05:53.550 person ID

But when I change the numeric input to alphabet, I cannot log the error. I just get the message. eg.
declare @v_out_result_num int;
declare @v_out_result_msg  varchar(1000);
exec [dbo].[p_set_OPD_Registration] "testing", 'av', @v_out_result_num output, @v_out_result_msg output
select  @v_out_result_num , @v_out_result_msg; 

input AGE field as 'AV'
In this case I get the message, but no error logging.

Msg 8114, Level 16, State 5, Procedure p_set_OPD_Registration, Line 47
  Error converting data type varchar to numeric.

Please help me how should I log such errors.
Any help will be highly appreciated. Thank you!

A:

On top of your SP you have this
ALTER PROCEDURE [dbo].[p_set_OPD_Registration] (
    @name_pr VARCHAR(120)
    ,@age NUMERIC(20, 4)
    ,@v_out_result_num INT OUTPUT
    ,@v_out_result_msg VARCHAR(1000) OUTPUT
    )

The paramter @age is defined as NUMERIC(20,4).
When you try to call the SP with a non-numeric value for age, this must fail...
You might define the parameter as string type and do the appropriate cast after validations...

