UMG Developer (6/1/2010)
Wow, I happened to be the first to answer it, and I got it right. 🙂Though not for the reason you stated, as it makes no difference if ANSI_NULLS is set ON or OFF that CASE statement will always return EQUAL, as NULL is always IS NULL. (Or at least as far as I can tell.)
If the CASE statement had been NULL = NULL the story would have been different.
SET ANSI_NULLS OFF
SET XACT_ABORT ON
GO
CREATE PROCEDURE DBO.x
AS
SET ANSI_NULLS OFF
SELECT CASE WHEN NULL = NULL THEN 'EQUAL' ELSE 'NOT EQUAL'
END AS ANSWER
SET ANSI_NULLS ON
SELECT CASE WHEN NULL = NULL THEN 'EQUAL' ELSE 'NOT EQUAL'
END AS ANSWER
GO
SET ANSI_NULLS OFF
EXEC DBO.X
SET ANSI_NULLS ON
EXEC DBO.X
DROP PROCEDURE DBO.X
This also gives the same answer please check it.and read explanation carefully.
Malleswarareddy
I.T.Analyst
MCITP(70-451)