I had one of those (all-too common) moments today that I spent scratching my head at a page of SQL code that was failing for no apparent reason. What's worse is the EXACT same code worked in another window. After a good deal of poking and prodding I figured out what was wrong and it involved my ANSI_NULLS settingI was working on an SQL Server 2000 function that accepted in some parameters and spit out a result set. The working version was the guts of the function extracted with the inputs declared as test variables at the top. This version worked fine. However, the exact same code in another window which was part of an ALTER FUNCTION statement and then a line of code calling the function from above returned unexpected results. Now Microsoft went the extra mile to make this problem as hard as freaking possible to debug by not allowing you to PRINT, SELECT (outside of your RETURN), or INSERT into a debugging table from a function. Since the function was the only place the code didn't seem to work, I was forced to debug the problem there by selecting values into the returned result set and playing with it until I had isolated the problem. I was getting more records than I expected and finally boiled it down to an INNER JOIN which should have been returning nothing, but seemed to be matching every record. It looked something like this: 
[code]SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.cola = t2.colb
	AND ts.colc = @param1
[/code]
Now, the reason I know the join should be producing zero records is because the value of @param1 was NULL. Any =, <>, >, or< comparison to NULL always returns false. Nothing is ever equal to NULL. Nothing is ever not equal NULL. Heck, NULL doesn't even equal NULL. This is why you must use the special operator "IS" like so: 
[code]IF @param1 IS NULL[/code]
This is the ISO standard. SQL Server provides a setting that changes NULLs to be treated as empty strings (Much like ColdFusion's handling of NULL values). 
[code]SET ANSI_NULLS OFF[/code]
When I had scripted out my function in Query Analyzer, it added the usual ANSI_NULL and QUOTED_IDENTIFIER setting before and after the ALTER statement. For some reason it was turning ANSI_NULLS OFF prior to altering the function and as a result the function was not handling NULLs in the way I was expecting. The other window I had been testing in was using a connection with the default setting of ANSI_NULLS turned ON so it behaved as expected. Frankly, I've never turned the ANSI_NULLS setting off on purpose before, and I'm not sure why Query Analyzer decided it should turn that setting off while altering my function but now I know one more thing to check before banging my head against the wall. By the way, here's how you can check a given setting like ANSI_NULLS to see what it is set to for your connection: 
[code]SELECT SESSIONPROPERTY ('ANSI_NULLS')[/code]
A value of "1" is "ON" and a value of "0" is "OFF".