Why not just write one stored procedure, GET_COMPANIES, and pass to it the
type of search you want, followed by the search argument? If the arguments
are of different variable types, cast them in the procedure accordingly, but
pass them in as strings?

Then there is no need to worry about the WHERE clause in your SP (unless you
specifically want to do this).

Myles

===========================
Myles Wakeham
Director of Engineering
Tech Solutions Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440
Web: www.techsol.org

> -----Original Message-----
> From: marcusmonaghan50 [mailto:marcus.monaghan@...]
> Sent: Wednesday, February 02, 2005 4:47 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Stored Procedure Querying
>
>
>
> 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
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>