Craig L Russell <[EMAIL PROTECTED]> writes:
> Hi Marieke,
>
> You have hit one of the big usability issues with SQL. While many
> vendors have implemented "WHERE value = ?" such that it behaves
> exactly like "WHERE value IS NULL" in case the parameter passed is
> null, it isn't required by the governing standard, and is not
> therefore a bug in an implementation but a feature.
>
> To be portable, you need to have different SQL statements in the case
> where the parameter is null versus not null. And if you have "n"
> parameters, you might have to have "2 ^ n" different SQL statements,
> depending on whether each of the parameters is null or not.
>
> Sadly, this is the state of the art in today's database world.
Hi Craig,
Let's not get too negative. I don't think you need more than one
statement.
This is the simplest one, but it requires two calls to setNull/setXXX
per parameter:
select * from mytable where value = ? or (? is null and value is null)
Alternatively, this query requires only one setNull/setXXX per
parameter and handles null:
select * from (values (cast (? as varchar(128)))) params(value),
mytable
where mytable.value = params.value or
(params.value is null and mytable.value is null)
Not exactly beautiful, but it works! :)
--
Knut Anders
