What is the best way to find out if a record exists in an SQL database?

[Nick Tentomas]

Hi,

I was wondering what is the best way to find out if a record exists in
an SQL database. Currently I run a "SELECT" query and I look for the
recordcount but I am not sure if this is the best way of getting this
info back. I need something generic enough to work with every SQL
server.

Thanks in advance,

Nick

[Reddy Palle]

Nick,
You can get test for the emptiess of the query opened, becuase all servers
might not have recordcount support.

-- Reddy Palle.

const
ExistsQuery= 'Select count(*) from %s where <%s>'

function RowExists(const PrimaryKeySearchClause, Table: string): Boolean;
var
Q: TQuery;
begin
Result := False;
Q := TQuery.Create(Self);
try
Q.Sql.Add(Format(ExistsQuery, [Table, PrimaryKeySearchClause]));
Q.Open;
Result := not Q.IsEmpty;
finally
Q.Free;
end;
end;

-- Reddy Palle.

"Nick Tentomas" <Nten...@CSBFACMGT.CSBFM.DAL.CA> wrote

[Eduardo Martinez Ocampo]

If you use count(*) you'll always get 1 record, you only have to check if
the result value is equal to 0

with Query1 do begin
SQL.Clear;
SQL.Add('select count(*) from yourtable where yourcondition = True');
SQL.Open;
if Fields[0].AsInteger = 0 then ShowMessage('No records found');
end;

--
Ing. Eduardo Martinez Ocampo
Chief of Information Systems
Industria Envasadora de Queretaro, S.A, de C.V.
"The Coca-Cola Canning Plant"
Queretaro, Qro Mexico

[Reddy Palle]

Eduardo,
Thanks for the correction.
-- Reddy Palle.

"Eduardo Martinez Ocampo" <edua...@nospam.ieqsa.com.mx>

[Ping Kam]

Nick Tentomas <Nten...@CSBFACMGT.CSBFM.DAL.CA> wrote in message
news:3A6F24E3...@CSBFACMGT.CSBFM.DAL.CA...

If all you want is to check if one specific record exists or not, the
following is the most efficient way:

with Query1 do
begin
SQL.Clear;

SQL.Add('Select 1 from table where ... );
Open;
if not EOF then
// record exists
else
// record does not exist
end;

HTH,
Ping Kam