Hi I have a value that I want to make sure it is only numeric in stored procedure. I have following code
if (IsNumeric(myField) =0)
begin
--does some work
end;
It should check myField if the number is non numeric it does work if not it just continues. For some reason above code is throwing error. Any idea how to fix that. Error is that exception was thrown varchar to int. Please let me know Thanks
Your expression is valid, I suspect that you are getting a value that is considered a numeric by the function, but cannot be converted to an integer. Try the following...
declare @myfield varchar(20)
set @myfield='.'
if ISNUMERIC(@myfield)=1
begin
select CONVERT(int,@myField)
end
The convert statement will blow up with the error your are reporting...
Check this question out: T-sql - determine if value is integer
Some more examples of "Numeric" values that cannot be converted to an integer
select '1.e0',ISNUMERIC('1.e0') as IsNum
union
select '.',ISNUMERIC('.') as IsNum
union
select '12.31',ISNUMERIC('12.31') as IsNum
Add a select convert(int,myField) in the begin/end to see the actual field value causing the error to occur
As others have pointed out, isnumeric() will return 1 on occasions where the string cannot be converted.
The following tests for a positive integer:
where myfield not like '%[^0-9]%'
The following tests for a positive integer or floating point number:
where myfield not like '%[^0-9.]%' and myfield not like '%.%.%'
As a note, none of these methods test for numeric overflow. You could test for that with something like:
where myfield not like '%[^0-9]%' and len(myfield) <= 10 and myfield <= '2147483647'
(assuming there are no leading 0s).
Check it like this instead:
if (IsNumeric(myField))
begin
--does some work
end;
IsNumeric is a function that returns 0 or 1. It doesn't return a boolean result that can be used like that.
ISNUMERICreturns anint