At 11:47 AM 2/02/2005 +0000, you wrote:

>Could anyone give me an indication of how they use stored procedures
>and querying. I'm after a best practice answer.
>
>If I have a stored procedure such as GET_COMPANIES which (as the name
>suggests) returns a list of companies. If I wanted to query this
>result set I would simply use a standard select: SELECT [fields] FROM
>GET_COMPANIES WHERE [where clause].
>
>I would like to know whether this is the best practice or whether the
>better way is to have multiple stored procedures that work on the
>indexes. For example:
>1. SELECT [fields] FROM GET_COMPANIES_BY_CODE([the company code])
>2. SELECT [fields] FROM GET_COMPANIES_BY_NAME([the company name])
>3. SELECT [fields] FROM GET_COMPANIES_BY_POSTCODE([the company postcode])
>4. SELECT [fields] FROM GET_COMPANIES_BY_NAME_POSTCODE([the company
>name, the company postcode])
>
>As you can see this will mean I'll have loads of stored procedures,
>but I presume better index usage on the database. I don't know this
>for sure, but I presume if I ran "SELECT [fields] FROM GET_COMPANIES
>WHERE [where clause]" the stored procedure would get all of the
>companies and would then filter the records. Whereas if I ran "SELECT
>[fields] FROM GET_COMPANIES_BY_CODE([the company code])" and the
>stored procedure used the COMPANY_CODE_IDX index of the company table,
>it would be more efficiant.

The first thing to understand is that any choices made about index usage
will occur when SELECT statements during execution of the stored procedure
are prepared, and not at all during the fetching of rows back to the
client. Using a WHERE clause with a selectable stored procedure is *worst*
practice. Your approach is just one big waste of resource and processing
cycles, since the entire FOR loop will execute and just discard the rows
that don't meet the search criteria. If you can get those sets directly
with parameterised DSQL statements, don't go down the stored procedure road
at all.

If, on the other hand, you want to use a SP to get a set that you would
otherwise not be able to get directly with a DSQL statement, or that can be
more efficiently got by nesting some FOR SELECT loops, then the selectable
procedure approach makes sense. However, design the procedure so that
*all* of the likely search criteria are input arguments. Then, inside the
SP, apply the input args as the arguments of the WHERE criteria.

If needed, use dummy values in the arguments you don't want considered for
a specific call to the SP. It means that the SP will contain 4 conditional
outer-level FOR SELECT statements with varying WHERE clauses, of which one
will be chosen on the basis of the input arguments that test out not to be
a dummies.

For example,
create procedure get_companies(
cmpcode integer,
cmpname varchar(50),
postcode char(8) )
returns (
........)

Then

1. select <whatever> from get_companies(1234567, 'dummy', 'dummy')
(dummy test results in WHERE cmpcode = :cmpcode)
2. select <whatever> from get_companies(-1, 'Acme Software, Inc', 'dummy')
(dummy test results in WHERE cmpname = :cmpname)
3. select <whatever> from get_companies(-1, 'dummy', '123-4567')
(dummy test results in WHERE postcode = :postcode)
4. select <whatever> from get_companies(1234567, 'Acme Software, Inc',
'123-4567')
(dummy test results in WHERE...AND...AND...)

./hb