How does one catch the error message "Error Converting data type varchar to decimal"

I have a stored procedure that performs an update with a try catch block. The column (discountPercentage) is set as a decimal data type. I want the stored procedure to catch if the user inserts the discountPercentage as anything other than a decimal, for example if they try to insert 45%. I'm using the ERROR_MESSAGE function and for some reason it's not catching the error message. I still receive the error message that states "Error converting data type varchar to decimal." Here is my code so far:

alter procedure procUpdateFoodTitleConditionDiscount
(
    @foodTitleId int,
    @conditionId int,
    @discountPercentage decimal(4,2),
    @errorMessage varchar(100) output
)
as 
begin
set @errorMessage = 'Discount updated'  

begin try
    update Discount
    set discountPercentage = @discountPercentage
    where foodTitleId = @foodTitleId and conditionId = @conditionId

    if (@@ROWCOUNT = 0)
    begin
        set @errorMessage = 'Update not successful.'
    end
end try

begin catch
    if (ERROR_MESSAGE () like '%converting%')
    begin
        set @errorMessage = 'Please Insert Discount Percentage as a Decimal (ex 0.45)'
    end
end catch

end;

declare @errorMessageValue varchar (100)
execute procUpdateFoodTitleConditionDiscount
    @foodTitleId = '104', 
    @conditionId = '4',
    @discountPercentage = '45%', 
    @errorMessage = @err开发者_运维技巧orMessageValue output
print @errorMessageValue

Assuming your attempting something like this

Exec procUpdateFoodTitleConditionDiscount 1,1,'45%'

The signature of your procedure is preventing any of the proc from being executed.

For example if you added

begin
PRINT 'foo'

set @errorMessage = 'Discount updated'  

'foo' would never be printed.

If you want the behavior you've described you'll need to change @discountPercentage decimal(4,2), to @discountPercentage varchar(10)

You can't trap this error in the proc: it happens before your code runs.

This needs cleaned in the client code. After all 45% is 0.45. SQL Server simply isn't good at this

You could change the parameter to varchar and do some manipulation but that's just plain silly