Ok, I see what you are doing. How efficient is it to call
the 2 selects? Is there no way that would only use one
select maybe like:

xcat_part = '';
select first 1 cat_part
from cat
where cat_part = '0L0821'
into :xcat_part;
CountRowsReturned = Row_Count;

I know that Row_Count doesn't work here, but is there something
like it that would return the number of rows in the result set.

This will run a lot and most of the time the record will exist.

Thanks

> -----Original Message-----
> From: GrumpyRain [mailto:s3057043@...]
> Sent: Tuesday, November 16, 2004 6:40 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: What is the BEST method to
> find if a record exists?
>
>
>
>
> I assume that testfield is unique here, and that you
> anticipate only one record returned for your test. If it is
> possible to have multiple records returned, use a for select
> statement instead of the select. By the way, it may be easier
> to read and debug later if you change NO_RECORD_EXISTS to
> simply RECORD_EXISTS and reverse the logic. I would make sure
> testfield is indexed if this is run frequently or on a large table.
>
> If you are inside the stored procedure, the following is
> probably fine.
>
> if exists(select 1 from testfile where testfield='xxxxx') then begin
> select testfield from testfile where testfield='xxxxx'
> into :testfield ;
> NO_RECORD_EXISTS = 'FALSE';
> end
> else
> begin
> NO_RECORD_EXISTS = 'TRUE';
> end
>
>
> --- In firebird-support@yahoogroups.com, "Michael L. Horne"
> <guardian@p...> wrote:
> > yes, that should work.
> >
> > Ok, should have mentioned this but what if I need some
> > values from the record if it exists?
> >
> > Thanks
> >
> > > -----Original Message-----
> > > From: jjochoa75 [mailto:jochoa@t...]
> > > Sent: Tuesday, November 16, 2004 5:29 PM
> > > To: firebird-support@yahoogroups.com
> > > Subject: [firebird-support] Re: What is the BEST method to
> > > find if a record exists?
> > >
> > >
> > > Try
> > >
> > > if exists(select 1 from testfile where testfield='xxxxx') then ...
> > >
> > >
> > > Juan Jose
> > >
> > > --- In firebird-support@yahoogroups.com, "Michael L. Horne"
> > > <guardian@p...> wrote:
> > > > Hello,
> > > >
> > > > I need to check to see if a record exists from inside a stored
> > > > procedure. What is the best code to use?
> > > >
> > > > The methods I know of are:
> > > >
> > > > 1.
> > > > NO_RECORD_EXISTS = 'FALSE';
> > > > select count(*) from testfile where testfield='xxxxx'
> > > into :numrecords;
> > > > if (numrecords = 0) then NO_RECORD_EXISTS = 'TRUE';
> > > >
> > > > 2.
> > > > NO_RECORD_EXISTS = 'FALSE';
> > > > for select testfield from testfile where testfield='xxxxx'
> > > into :testfield
> > > > do begin
> > > > NO_RECORD_EXISTS = 'TRUE';
> > > > end
> > > >
> > > > 3. /* this one doesn't seem to work correctly and I am not sure
> > > why */
> > > > NO_RECORD_EXISTS = 'FALSE';
> > > > select first 1 testfield from testfile where testfield='xxxxx'
> > > > into
> > > > :testfield; if (testfield = null) then NO_RECORD_EXISTS
> = 'TRUE';
> > > >
> > > > -------
> > > > But these all seem like a difficult set of things to do to find
> > > out if a
> > > > record exists, so is there a better way and what is it?
> > > >
> > > > Thanks
> > > > Michael L. Horne
> > >
> > >
> > >
> > >
> > >
> > > ------------------------ Yahoo! Groups Sponsor
> > > --------------------~-->
> > > $9.95 domain names from Yahoo!. Register anything.
> > > http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/67folB/TM
> > > --------------------------------------------------------------
> > > ------~->
> > >
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
>
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> Make a clean sweep of pop-up ads. Yahoo! Companion Toolbar.
> Now with Pop-Up Blocker. Get it for free!
> http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/67folB/TM
> --------------------------------------------------------------
> ------~->
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>