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.

Hope that makes sense.

All replies are welcome.

Regards,
Marcus