SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 02:21 AM
| ||||
| ||||
 Prepared Statement where value IS null or value = :x 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      |
|
02-29-2008, 02:21 AM
| |||
| |||
| Re: Prepared Statement where value IS null or value = :x 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) |
|
02-29-2008, 02:21 AM
| |||
| |||
| Re: Prepared Statement where value IS null or value = :x kevin_g_frey@hotmail.com (Kevin Frey) wrote in message news:<e5d907ad.0404042246.2d000353@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 |
|
02-29-2008, 02:21 AM
| |||
| |||
| Re: Prepared Statement where value IS null or value = :x 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 kevin_g_frey@hotmail.com (Kevin Frey) wrote in message news:<e5d907ad.0404042246.2d000353@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 |
|
02-29-2008, 02:22 AM
| |||
| |||
| Re: Prepared Statement where value IS null or value = :x 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) |
|
02-29-2008, 02:22 AM
| |||
| |||
| Re: Prepared Statement where value IS null or value = :x > 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. |
|
02-29-2008, 02:23 AM
| ||||
| ||||
| Re: Prepared Statement where value IS null or value = :x kevin_g_frey@hotmail.com (Kevin Frey) wrote in message news:<e5d907ad.0404051605.733738cd@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. |
Linear Mode
