--- In ib-support@y..., Lucas Franzen <luc@r...> wrote:

>
>
> Burak ÖZLER schrieb:
> >
> > Here is the procedure
> > CREATE PROCEDURE CARI_BORCUPDATE (
> > CARIID INTEGER,
> > BORC NUMERIC(17,2))
> > RETURNS (
> > OLDU SMALLINT)
> > AS
> > declare variable ESKIBORC NUMERIC(17,2);
> > begin
> > SELECT BORC
> > FROM CARI
> > WHERE CARIID = :CARIID
> > INTO :ESKIBORC;
> > if ISNULL(ESKIBORC) then ESKIBORC = 0;
> > ESKIBORC = ESKIBORC + :BORC;
> > Update Cari Set Borc = :ESKIBORC
> > where CariID = :CariID;
> > OLDU = 1;
> > SUSPEND;
> > end
> >
> > if ESKIBORC returns NULL
> > Interbase can't make the calculation
> > ESKIBORC = ESKIBORC + :BORC;
> > so I have to make a NULL control but I know that there's no isnull
> > function in Interbase.
> >
> > The Procedure above is a very simple procedure and I have to write
> > very complex procedures.
> >
> > 1- What is the easiest way to control Null's ?
> change your isnull to:
> IF ( ESKIBORC IS NULL ) THEN ESKIBORC = 0;
>
> Interbase DOES HAVE a IS NULL function!
>
> > 2- How can I determine that the first select could find a record.?

(I

> > tried WHEN SQLCODE but no success)
>
> If you want to be sure that a record was found at all, do also

select

> the primary key from the underlying table into a local variable.
> If this is null, then you can be sure, no record was found.
>
> So try to change your procedure to:
>
> CREATE PROCEDURE CARI_BORCUPDATE (
> CARIID INTEGER,
> BORC NUMERIC(17,2))
> RETURNS (
> OLDU SMALLINT)
> AS
> declare variable ESKIBORC NUMERIC(17,2);
> declare variable PKCOL INTEGER;
> begin
>
> PKCOL = NULL;
>
> SELECT BORC, CARIID
> FROM CARI
> WHERE CARIID = :CARIID
> INTO :ESKIBORC, :PKCOL;
>
> IF ( ESKIBORC IS NULL OR PKCOL IS NULL ) THEN ESKIBORC = 0;
>
> ESKIBORC = ESKIBORC + :BORC;
>
> IF ( PKCOL IS NOT NULL ) THEN
> BEGIN
> Update Cari Set Borc = :ESKIBORC where CariID = :CariID;
> END
> OLDU = 1;
> SUSPEND;
> end
>
>
> hth
>
> luc.
>
> ps.:
>
> unless this is just a code snippet of a StoredProc that's doing more
> than this update, wouldn't it be easier to issue:
> UPDATE Cari Set Borc = BORC + :YOURVALUE where CariID = :CariID;
>
> ??

Yes You're right at the begining, my procedure was like your sentence

UPDATE Cari Set Borc = BORC + :YOURVALUE where CariID = :CariID;

but if the field borc have null value the result doesn't change again
NULL

I'll try to remove allow null property for the fields that I would
make this kind of calculations.

thanks for your interest..
Burak