first :
Hello ladies and gentelmans,

second :
I "hijacked" the other thread because I am new to mailing lists and didn't know exactly how to send message. Sorry for that. I send the message 3 times because the same reason.

third:
Thank you Mr. Frazen. This will help but my problem was that the parameters aren't nulls. They are empty strings. Everything works fine when I check for empty string. :)

----- Original Message -----
From: Lucas Franzen
To: firebird-support@yahoogroups.com
Sent: Friday, December 19, 2003 4:51 PM
Subject: Re: [firebird-support] Stored procedure SQL problem



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.





To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.




[Non-text portions of this message have been removed]