Svilen,

Svilen Stefanov schrieb:

> Hi guys ,

First:
there are not only guys here.

Second:
it's not likely to hijack other threads, if you don't answer to a
thread, start a new one, please.

Third: going to answer ;-)

>
> I want to write stored procedure to do the following thing. :
> select * from Table1 where Column1 = :Param1 , Column2 = :Param2 .........
>
> but if the Param1 = null then I want the procedure to exclude " Column1 =
> :Param1". So sql code will be something like :
>
> select * from Table1 where Column2 = :Param2

> I thought that "case" statement will do the job :
>
> select * from Table1 where
> Column1 = case :Param1 is null then Column1 else :Param1 end and
> Column2 = case :Param2 is null then Column2 else :Param2 end
>
> In this SQL If Param1 is null then then select must be "select * from

Table1 where Column1=Column1 and Column2=:Param2" . If I execute this
in SQL editor everything is OK , but the stored procedure with "case"
statement doesn't work like this "select" statement. Is this a bug or I
am missing something.

>
> Keep in mind that I have many params and many columns. Do you think

this is

> possible with FireBird 1.5 ?
>

Keep in mind: since StroedProcs have precompiled DML you cannot make
your select dynamical!

What you can do is:

SELECT * from TABLE
WHERE
( COLUMN1 = :PARAM1 OR :PARAM1 IS NULL ) AND
( COLUMN2 = :PARAM2 OR :PARAM2 IS NULL ) AND
....
( COLUMN_N = :PARAM_N OR :PARAM_N IS NULL )
....


And another note:

Since I haven't figured out what this proc is doing anyway (SELECT * is
not a good way to write SP's. It will work if you have a return
parameter for every column but it will break as soon as you add a new
(or drop a) column for/from the underlying table.

Better is always to do a
SELECT <FIELDLIST>
FROM TABLE
INTO <RETURN_FIELDLIST>

HTH

Luc.