Check out NULL Comparison Search Conditions and SET ANSI_NULLS in the BOL.
>(2) When I go to Query Analyzer it seems to have a mind of its own as far as setting the Ansi Nulls command at the beginning of editing a stored procedure. Sometimes its On and sometimes its Off.
From BOL:
"SQL Query Analyzer uses the SQL Server ODBC driver, which, by default, sets these SQL-92 options on: SET ANSI_WARNINGS, SET ANSI_PADDING, and SET ANSI_NULLS. Any errors returned are formatted as ODBC errors rather than DB-Library errors."
>(3) Does each stored procedure have its own private environment for Ansi Nulls setting.
From BOL:
"The settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS are saved when a stored procedure is created or altered. These original settings are enabled when the stored procedure is executed, and any client session settings are restored afterward. Within the stored procedure, any changes to SET ANSI_NULLS do not take effect until after the stored procedure executes. "
In general, my overall opinion is that if you're going to check for a NULL, use an explict IS (NOT) NULL and don't screw with =NULL. There are too many ways that things can go wrong.
-Mike