On Sat, 10 Jan 2004 17:11:42 +0000 (UTC), M Tuttle wrote:

> ...
> I have many setup code tables that I allow the user to add and delete values
> from. When they want to delete a record, I call a stored procedure to hit
> any table(s) the code may have been used in to be sure that it was not used
> before I allow them to delete. I basically check the first table and is a
> hit is found, I exit the procedure and deny the delete. If a hit is not
> found in the first table, I go to the next table and repeat as necessary.
>
> A typical stored procedure may look like this:
> ...
> /* See if we have a CLIENT_SITE record association on file */
> SELECT COUNT(*)
> FROM CLIENT_SITE CS
> WHERE CS.DEBT_TYPE_SUB_CODE = :v_DEBT_TYPE_SUB_CODE
> INTO :iRowCount;
>
> IF (iRowCount > 0) THEN
> ...

Depending on the situation, 'select count(*) ...' may be slow.
Using something with 'exists' might prove an improvement - I assume you are
just testing for the existence of one (or more) records. Maybe 'select 1
first 1 ...' will be better as well.
If there is an appropriate index and the resultset is very small the
difference may be imperceptible, but do try an alternative.


--
Aage J.