You could have some enumerated type

1. GET_COMPANIES_BY_CODE
2. GET_COMPANIES_BY_NAME
3. GET_COMPANIES_BY_POSTCODE
4. GET_COMPANIES_BY_NAME_POSTCODE

set term ^;

create procedure "GET_COMPANIES"
(
iType integer,
iParam1 varchar(100),
iParam2 varchar(100)
)
RETURNS
(
CompanyID integer,
CompanyName varchar(100)
)
AS
DECLARE VARIABLE VID INTEGER;
DECLARE VARIABLE VNAME VARCHAR(100);
BEGIN
IF (iType = 1) THEN
BEGIN
FOR SELECT ID, NAME
FROM COMPANY
WHERE CODE = :iParam1
INTO :VID, :VNAME
DO
BEGIN
COMPANYID = :VID;
COMPANYNAME = :VNAME;
SUSPEND;
END
END
ELSE IF (iType = 2) THEN
BEGIN
etc
END
END
^

Then your query would be something like

select CompanyID, CompanyName
from GET_COMPANY(1, '123');


It is best to have your stored procedure doing the where clause for
you, otherwise the procedure will need to fetch every record before
it can filter (so the underlying index will not be used). If the
stored procedure runs a query with a where clause, that where clause
will use any available index.

The main restriction with reusing the same procedure for the
different queries is that their parameters will need to look pretty
much identical. You can get away with casting things as varchars and
vice versa, but I generally find it easier to have a different
procedure for each one.

You shouldn't use a stored procedure for the sake of using one
though. There is no harm in actually running a query from your
application. I use stored procedures when the query is common, when
it is a bit more complex and I dont trust the developer to query the
right fields, when a group of fields in different tables need to be
impacted in a single transaction, when you don't want to give
permissions to directly work with the table, when certain basic
arithmetic is required on the dataset or if the query needs to run
inside a trigger or another procedure.

In the case of get_companies, I would probably leave it for a normal
query unless there is complex and specific conditions that must be
met before a company can be considered.

Adam


--- In firebird-support@yahoogroups.com, "marcusmonaghan50"
<marcus.monaghan@b...> 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.
>
> Hope that makes sense.
>
> All replies are welcome.
>
> Regards,
> Marcus