Re: Arithmetic overflow error converting varchar to data type numeric.

From: Betty Ng (anonymous_at_discussions.microsoft.com)
Date: 06/16/04 

Date: Wed, 16 Jun 2004 00:07:55 -0700

Million thanks ...

>-----Original Message-----
>In addition to Steve's suggestion, you might want to add
isNumeric() to your
>where clause to ensure the value is convertible.
>
>
>"Betty NG" <anonymous@discussions.microsoft.com> wrote in
message
>news:1d08001c45361$3d406520$a501280a@phx.gbl...
>> Thanks for your reply.
>>
>> I have tried what you had advised. The result is ok.
>>
>> This is my select sql in the sp:
>>
>> SELECT convert(numeric(6,2),mycol), sum(sales_units)
>> FROM mytable
>> WHERE convert(numeric(6,2),mycol) between 0 and 4.90
>> group by convert(numeric(6,2),mycol)
>> order by convert(numeric(6,2),mycol)
>>
>> where mycol is defined as varchar(5) in the mytable.
>>
>> I have managed to get the result set when it was
executing
>> from the SQL Query Analyzer. But when execute from sp,
>> the error message is "Server: Msg 8115, Level 16, State
6,
>> Procedure, Line 88
>> Arithmetic overflow error converting varchar to data
type
>> numeric."
>>
>> Do you have any insights?
>>
>> Thanks
>> Betty
>>
>> >-----Original Message-----
>> >check to see if the data is actually valid for
conversion.
>> >
>> >e.g.
>> >select convert(numeric,col_name)
>> >from tb
>> >where isnumeric(col_name)=1
>> >
>> >"Betty NG" <anonymous@discussions.microsoft.com> wrote
in
>> message
>> >news:1d00101c4534e$e82638b0$a501280a@phx.gbl...
>> >> Good morning.
>> >>
>> >> I have the above mentioned error message during the
>> >> execution of stored procedure in SQL Server 2000. Is
>> >> there any workaround?
>> >>
>> >> Thanks n regards
>> >> Betty
>> >
>> >
>> >.
>> >
>
>
>.
>

Relevant Pages