Thanks,

Actually I figured out why the option 3 in the initial
post did not work, it was not because the variable
matched the column it was because the variable
needed to be initialized each time. It is not set
if the select returned zero rows.

should have been
3.
NO_RECORD_EXISTS = 'FALSE';
testfield = null;
select first 1 testfield from testfile
where testfield='xxxxx' into :testfield;
if (testfield = null) then NO_RECORD_EXISTS = 'TRUE';

Thanks

> -----Original Message-----
> From: Bogus³aw Brandys [mailto:brandys@...]
> Sent: Wednesday, November 17, 2004 6:11 AM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Re: What is the BEST method
> to find if a record exists?
>
>
>
> Michael L. Horne wrote:
> > 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
> >>>>
>
> If You like to know value of testfield Option 3 as You
> describe is good
> ,but with modification
>
> 3. /* this one doesn't seem to work correctly and I am not
> sure why */ declare variable EX CHAR(1);
>
> select first 1 testfield from testfile where
> testfield='xxxxx' into :EX; if (EX IS NULL) then ......
>
> You know now why it was not working (testfield was variable and also
> column) ;-)
>
> (Beside 'first 1' is not necessary if your testfield is
> primary key,but
> in the case above it is needed)
>
> Regards
> Boguslaw Brandys