Prepared Statement where value IS null or value = :x

[Kevin Frey]

Hello,

I have a table which contains some nullable columns. I want to write a
single query, which can be prepared (ie. prepared statement), that can
handle null or non-null values for the where clause. Is this possible
in a standard-conforming manner?

The simple for of the query is this:

SELECT * FROM <table> WHERE <column> = <value>

But when the value to be matched on is NULL, the syntax of the query
must change to be:

SELECT * FROM <table> WHERE <column> IS <value>

In the second case <value> is NULL.

I know one option might be to change the ANSI NULL handling option,
but I am loathe to do this (I have five RDBMS's to support, not just
MSSSQL).

I thought I might have been able to cheat using an IN clause to make
the SQL consistent, but no luck.

Thanks

Kevin

[Hugo Kornelis]

On 4 Apr 2004 23:46:17 -0700, Kevin Frey wrote:

Hello,

I have a table which contains some nullable columns. I want to write a
single query, which can be prepared (ie. prepared statement), that can
handle null or non-null values for the where clause. Is this possible
in a standard-conforming manner?

The simple for of the query is this:

SELECT * FROM <table> WHERE <column> = <value>

But when the value to be matched on is NULL, the syntax of the query
must change to be:

SELECT * FROM <table> WHERE <column> IS <value>

In the second case <value> is NULL.

I know one option might be to change the ANSI NULL handling option,
but I am loathe to do this (I have five RDBMS's to support, not just
MSSSQL).

I thought I might have been able to cheat using an IN clause to make
the SQL consistent, but no luck.

Thanks

Kevin

The syntax you show in the subject of this message should work:

SELECT <columns>
FROM <table>
WHERE <column> = <value>
OR <column> IS NULL

If that doesn't help you, please post actual DDL and sample data (in
the form of insert statements) to help us reproduce and then solve
your problem.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Mystery Man]

ke**********@hotmail.com (Kevin Frey) wrote in message news:<e5**************************@posting.google. com>...

Hello,

I have a table which contains some nullable columns. I want to write a
single query, which can be prepared (ie. prepared statement), that can
handle null or non-null values for the where clause. Is this possible
in a standard-conforming manner?

The simple for of the query is this:

SELECT * FROM <table> WHERE <column> = <value>

But when the value to be matched on is NULL, the syntax of the query
must change to be:

SELECT * FROM <table> WHERE <column> IS <value>

In the second case <value> is NULL.

I know one option might be to change the ANSI NULL handling option,
but I am loathe to do this (I have five RDBMS's to support, not just
MSSSQL).

I thought I might have been able to cheat using an IN clause to make
the SQL consistent, but no luck.

Thanks

Kevin

Try the following (assuming -1 isnt a possible value for <table>.<column>)

SELECT * FROM <table> WHERE IsNull(<column>, -1) = IsNull(<value>, -1)

I think this is sql-92 compatible as well

[Jay Allard]

Will one of these do the trick?

select * from <table> where ((column is null) or (column = value))

select * from <table> where isnull(column, value) = value


ke**********@hotmail.com (Kevin Frey) wrote in message news:<e5**************************@posting.google. com>...

Hello,

I have a table which contains some nullable columns. I want to write a
single query, which can be prepared (ie. prepared statement), that can
handle null or non-null values for the where clause. Is this possible
in a standard-conforming manner?

The simple for of the query is this:

SELECT * FROM <table> WHERE <column> = <value>

But when the value to be matched on is NULL, the syntax of the query
must change to be:

SELECT * FROM <table> WHERE <column> IS <value>

In the second case <value> is NULL.

I know one option might be to change the ANSI NULL handling option,
but I am loathe to do this (I have five RDBMS's to support, not just
MSSSQL).

I thought I might have been able to cheat using an IN clause to make
the SQL consistent, but no luck.

Thanks

Kevin

[Hugo Kornelis]

On 5 Apr 2004 05:32:06 -0700, Mystery Man wrote:

SELECT * FROM <table> WHERE IsNull(<column>, -1) = IsNull(<value>, -1)

I think this is sql-92 compatible as well

ISNULL is not sql-92 compatible. COALESCE is (and it does exactly the
same, plus offers some extra possibilites).

I never use ISNULL anymore.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Kevin Frey]

> Try the following (assuming -1 isnt a possible value for <table>.<column>)

SELECT * FROM <table> WHERE IsNull(<column>, -1) = IsNull(<value>, -1)

I think this is sql-92 compatible as well

Yes, this is the kind of solution I am looking for. Does anyone have
any idea how the query optimiser will handle this when it comes time
to choosing appropriate indexes? On a simple test it seems it will
still utilise the correct index, but I'm not particularly confident.

[Mystery Man]

ke**********@hotmail.com (Kevin Frey) wrote in message news:<e5**************************@posting.google. com>...

Try the following (assuming -1 isnt a possible value for <table>.<column>)

SELECT * FROM <table> WHERE IsNull(<column>, -1) = IsNull(<value>, -1)

I think this is sql-92 compatible as well

Yes, this is the kind of solution I am looking for. Does anyone have
any idea how the query optimiser will handle this when it comes time
to choosing appropriate indexes? On a simple test it seems it will
still utilise the correct index, but I'm not particularly confident.

A showplan indicates that the query optimiser does utilise the
appropriate indexes on our test cases. We have been using this
approach for autogenerated procs on different databases.