How to use IS NULL in a parametrized query (Delphi)

Question

I got statements like this:

SELECT * From Table WHERE Feld IS NULL
SELECT * From Table WHERE Feld IS NOT NULL

Now I'm wondering how I could parametrize this query:

SELECT * From Table WHERE Feld IS :Value

As I cannot pas 'NOT NULL' to a parameter, I think thats not possible at all - but maybe somebody knows a solution for that? Thanks!

score 6 · Accepted Answer · answered Aug 24 '12 at 09:58

6

You could try something like this (tested with Firebird 2.5):

SELECT * FROM TABLE WHERE (IIF(FIELD IS NULL, 'Y', 'N') = :IS_NULL)

then pass 'Y' or 'N' to the IS_NULL parameter.

Depending on the database you're using, you might need to replace IIF with a CASE or similar construct.

answered Aug 24 '12 at 09:58

Ondrej Kelle

Depending on the OP task, in Firebird may be used [SQL_NULL](http://www.firebirdsql.org/refdocs/langrefupd25-sqlnull.html) data type. But is it supported or not, depends on the data access library, the OP is using. – da-soft Aug 25 '12 at 12:03
@da-soft Interesting link, thank you! But what I understood from that page was that `SQL_NULL` could be used to specify optional parameters, not NULL-ness of queried data. It doesn't seem to be useful for this task. – Ondrej Kelle Aug 26 '12 at 05:33
It may be useful. For example, if `field` is INTEGER and all values are greater than 0, then the condition may be written: `FIELD > :p OR :p IS NULL AND FIELD IS NULL`. So, when :p=0 then equivalent to `IS NOT NULL`, when :p=NULL then quivalent to `IS NULL`. But again, it depends on the data access library, which supports or not supports SQL_NULL features. – da-soft Aug 26 '12 at 08:04
Want to add - this will work even without SQL_NULL support, but SQL_NULL support makes it more effective. – da-soft Aug 26 '12 at 08:17

1 Answers1