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!

Answer 1

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.