Hello Steve,

> I'd like to use a selectable stored procedure but I need to build a
> dynamic WHERE clause based on input parameters. For instance,
> assuming I have 8 char(1) parameters and 2 of them are 'T', I need
> something like:
>
> select the_field
> from the_table
> where ((some_field = some_integer_1) or (some_field = some_integer_2))
>
> I know I could also do:
>
> select the_field
> from the_table
> where (some_field IN (some_integer_1, some_integer_2))
>
> My question is, can I either build a WHERE clause dynamically from
> within a stored procedure OR can I pass a where clause as a parameter
> to a stored procedure?
>
> My procedure might look like:
>
> create procedure spMyProc(some_bool_1 char(1), some_bool_2 char(1),
> etc...)
> returns (the_result VarChar(30))
> AS
> declare varaible sWhere VarChar(100);
> Begin
> if (some_bool_1 = 'T') then
> Begin
> sWhere = '(some_field = 1)';
> End
> etc, etc, etc...
> for Select some_stuff
> from the_table
> where :sWhere
> into :the_result
> do
> Begin
> suspend;
> End
> End
>
> I don't think this would work, but how can I accomplish the same thing?

Well, a SQL statements can only use parameters where there are values.

If you want to do a dynamic statement, check out [FOR] EXECUTE STATEMENT
in the Release Notes of Firebird 1.5...

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com