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 = @errorMessageValue output
print @errorMessageValue
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 = @errorMessageValue output
print @errorMessageValue