--- 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 just joined this group but I need to respond anyway. I use SQL
Server a lot and often came across the same type of business case. I
came up with an elegant solution using CASE statements that allows me
to pass in any combination of the possible arguments.

I'm new to Firebird and don't quite yet fully grasp the syntax, but I
presume the following pseud-code should work:

Argument list to stored procedure:
COMPANY_CODE
COMPANY_NAME
COMPANY_POSTCODE

SELECT
*
FROM
COMPANIES
WHERE
CASE
WHEN COMPANY_CODE IS NULL THEN 1
ELSE
CASE
WHEN COMPANY_CODE = COMPANY_CODE_COLUMN THEN 1
ELSE 0
END
END = 1
AND CASE
WHEN COMPANY_NAME IS NULL THEN 1
ELSE
CASE
WHEN COMPANY_NAME = COMPANY_NAME_COLUMN THEN 1
ELSE 0
END
END = 1
AND CASE
WHEN COMPANY_POSTCODE IS NULL THEN 1
ELSE
CASE
WHEN COMPANY_POSTCODE = COMPANY_POSTCODE_COLUMN THEN 1
ELSE 0
END
END = 1

Does Firebird support something like this?

-John