On Sat, 4 Dec 2004 15:53:05 -0800, we7313 wrote:

>I have a proc that can specify a price range if its passed in.
>my and where clause looks like this:
>
>where ((PriceViewHotelPrice.price + PriceViewAirPrice.price)between 500 and
>600) or(NULL = NULL))
>
>I have removed the parameters & replaced them with the values passed in
>running it through Sql Analyzer & have confirmed the problem.
>If I replace NULL = NULL with 'a' = 'a' it works fine. Does anyone see the
>problem with this?

"Hugo Kornelis" wrote:

> On Sat, 4 Dec 2004 15:53:05 -0800, we7313 wrote:
>
> >I have a proc that can specify a price range if its passed in.
> >my and where clause looks like this:
> >
> >where ((PriceViewHotelPrice.price + PriceViewAirPrice.price)between 500 and
> >600) or(NULL = NULL))
> >
> >I have removed the parameters & replaced them with the values passed in
> >running it through Sql Analyzer & have confirmed the problem.
> >If I replace NULL = NULL with 'a' = 'a' it works fine. Does anyone see the
> >problem with this?
>
> Hi we7313,
>
> This is working as designed. Using ANSI-standard behaviour for NULLs, NULL
> is never equal to anything. Think of NULL as an unknown value. If you
> asked you if an unknown value is equal to an unknown value, would you say
> "yes", "no" or "no idea"?
> Welcome in the wonderfull world of three-valued logic!
>
> This being said, I'm not sure what you're trying to accomplish and why you
> are trying to do it this way. Could you explain your actual problem
> instead of presenting only a partial solution? There might be other, maybe
> even better ways to solve your problem.
>
> If you describe the problem, it would be helpfull to include table
> structure (as CREATE TABLE statements, including all constraints), sample
> data (as INSERT statements) and expected output - that often helps getting
> across what you try to do AND it's a useful testing aid.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

On Sun, 5 Dec 2004 07:07:01 -0800, we7313 wrote:

>Well now that you say that & sql books online confirms it, I see why it
>isn't working. The reason I do a statement like above is to apply optional
>criteria based on the parameter values passed in. Thanks for the help.