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