November 15, 2007 at 6:45 am
I initially saw the spec the same way James (that's what I get for not READING the spec). The issue was that NULLs do exist in the ID columns in the database and a technique for retrieving those as well as non-NULL values was needed.
With ANSI NULLs OFF the comparison to NULL is more akin to comparing to an empty string which works with logical operators. With ANSI NULLs ON logical operators do not work because (for the most part) comparisons with honest-to-goodness NULLs result in 'unknown' not true or false.
The combination of IS NULL and the logical operator(s), as Joe B contributed, allows for correctly matching both 'real' values and NULLs.
As far as your dynamic query code for 'ignoring' NULL parameters. I have a little test set up for various methods and will add yours to the 'suite'. Watch for another post with Dynamic Query as the title for code and my results.
November 15, 2007 at 7:45 am
Will do ... if it is e.g. MyID=IsNull(@MyID,MyID) then I would think it can still use any index on MyID rpovided @MyID was NOT null and the optimiser might be clever enough to ignore if @MyID was null - if it needs to be e.g. IsNull(MyID,0) = Coalesce(@MyID,MyID,0) to allow for Nulls in actual data (and in this example treating them as though MyID was 0) then it will blow away any hope of using an index
The advantage of this method is that it needn't be dynamic SQL
James Horsley
Workflow Consulting Limited
November 15, 2007 at 7:55 am
"I initially saw the spec the same way James (that's what I get for not READING the spec). The issue was that NULLs do exist in the ID columns in the database and a technique for retrieving those as well as non-NULL values was needed.
With ANSI NULLs OFF the comparison to NULL is more akin to comparing to an empty string which works with logical operators. With ANSI NULLs ON logical operators do not work because (for the most part) comparisons with honest-to-goodness NULLs result in 'unknown' not true or false.
The combination of IS NULL and the logical operator(s), as Joe B contributed, allows for correctly matching both 'real' values and NULLs."
This is correct in understanding the spec and that Joe B's suggestions does the jpb.
Jonathan
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply