How to deal with (maybe) null values in a PreparedStatement?
I've always done it the way you show in your question. Setting the same parameter twice is not such a huge hardship, is it?
SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL);
There is a quite unknown ANSI-SQL operator IS DISTINCT FROM that handles NULL values. It can be used like that:
SELECT * FROM tableA WHERE x NOT IS DISTINCT FROM ?So only one parameter has to be set. Unfortunately, this is not supported by MS SQL Server (2008).
Another solution could be, if there is a value that is and will be never used ('XXX'):
SELECT * FROM tableA WHERE COALESCE(x, 'XXX') = COALESCE(?, 'XXX')
would just use 2 different statements:
Statement 1:
SELECT * FROM tableA WHERE x is NULLStatement 2:
SELECT * FROM tableA WHERE x = ?You can check your variable and build the proper statement depending on the condition. I think this makes the code much clearer and easier to understand.
EDITBy the way, why not use stored procedures? Then you can handle all this NULL logic in the SP and you can simplify things on the front end call.