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
> >
> >
> >
> >
> >
> >
> >
> >
> >