Le 06/09/2007 09:06, Cipto a dit :

> Hi,
>
> I use FB 2.0.2 Superserver in WinXP SP2, and I have the following stored
> procedure:
> create procedure spNumeric
> as
> declare variable vn numeric(8,4);
> declare variable aa numeric(8,4);
> declare variable bb numeric(18,4);
> begin
> aa=65;
> bb=500;
> vn=cast(((3.14*(aa/2.0)*(aa/2.0)*500)/1000000) as numeric(8,4));
> end
>
> If I compile and run everything is OK, now if I change to this:
> CREATE PROCEDURE SPNUMERIC
> AS
> declare variable vn numeric(8,4);
> declare variable aa numeric(8,4);
> declare variable bb numeric(18,4);
> begin
> aa=65;
> bb=500;
> --The bb variable is used here instead of numeric constant
> vn=cast(((3.14*(aa/2.0)*(aa/2.0)*bb)/1000000) as numeric(8,4));
> end
>
> Compile is OK, but when I call this SP this error appear:
> Unsuccessful execution caused by system error that does not preclude
> successful execution of subsequent statements.
> Integer overflow. The result of an integer operation caused the most
> significant bit of the result to carry.
> At procedure 'SPNUMERIC'.
>
> Can I have explanation of using numeric operation in this case?
>

When you use 500 you are using an integer, when you use bb, you are using a
numeric(18,4)

perhaps you need some more casting, something like :

CREATE PROCEDURE SPNUMERIC
AS
declare variable vn numeric(8,4);
declare variable aa numeric(8,4);
declare variable bb numeric(18,4);
begin
aa=65;
bb=500;
--The bb variable is used here instead of numeric constant
vn=cast(((cast(3.14*(aa/2.0) as numeric(8,2))*(cast((aa/2.0)*bb)) as
numeric(8,2))/1000000) as numeric(8,4));
end