Never mind - I worked it out...(Couldn't get a case statement to work...)
The solution is so simple, it's not worth reading(I should have worked it
out sooner)

Here's the stored procedure if you like...
(Explanation: If you specify a search term, it uses it in the where,
otherwise, it ignores any filtering, and continues on)

set term !! ;
CREATE PROCEDURE sp_search (
str_namein VARCHAR(200)
) returns (
str_name VARCHAR(200)
AS BEGIN
FOR SELECT str_name
FROM tbl_table
WHERE
(
(:str_namein IS NOT NULL AND str_name CONTAINING ':str_namein')
OR
(:str_namein IS NULL)
)
INTO :str_name
DO SUSPEND;
END !!
SET TERM ; !!

With records with names 'henry','wilma','roger'
select * from sp_search(null) returns
henry
wilma
roger
select * from sp_search('wilma') returns
wilma
select * from sp_search('e') returns
henry
roger

> -----Original Message-----
> From: Nigel Weeks [mailto:nigel@...]
> Sent: Wednesday, 6 August 2003 11:12
> To: Firebird-Support (E-mail)
> Subject: [firebird-support] Case statements in where clause
>
>
> Does anyone know of/have any examples of using case
> statements in the where
> clauses of Select queries?
> I'm wanting to run different types of selects depending on
> nulls, and i'd
> rather on doing if blocks and multiple queries, as there'd be 2^n
> combinations of searches...
> If a parameter is not null, then it is used in the search,
> otherwise, any
> value of the field is returned...possible?
>
> select int_id, str_name, str_address
> from table
> where
> (case when int_id id is not null then
> int_id = :int_id /* Apply the provided filter */
> else
> 1 /* I think this makes the condition true, and lets the record
> match... */
> )
> and
> (case when str_name is not null then
> str_name = :str_name
> else
> 1
> )
> ...
>
> What version of Firebird were they introduced in?
>
>
> --------------------------------------------------------
> Nigel Weeks
> E-Easy
> 15 Wellington St. Launceston Tas 7250
> Ph. 61 3 6334 6664
> Fax. 61 3 6331 7032
> Email. nigel@...
> Web: www.e-easy.com.au
> --------------------------------------------------------
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~-->
> Rent DVDs from home.
> Over 14,500 titles. Free Shipping
> & No Late Fees. Try Netflix for FREE!
> http://us.click.yahoo.com/BVVfoB/hP.FAA/ySSFAA/67folB/TM
> --------------------------------------------------------------
> -------~->
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/