Handling NULL in SQL Server I am facing issue when trying to handle null.

Here is example

I have a table

Create table Test_Table 
(
id int null,
pName varchar(10)
)
GO


Id column has NULL value.

INSERT INTO Test_Table 
SELECT NULL, 'A' UNION 
SELECT NULL,'B' UNION 
SELECT NULL, 'C' UNION 
SELECT NULL,'D' 
GO


Now when I execute different select statements, last two conditions are failing. I can use ISNULL to handle it but there are approx 500 SPs which I would need to change.

So, I need solution apart from that, if possible.

SELECT * FROM Test_Table WHERE id is NULL --True

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=NULL --True


SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=id --False. Why???

Declare @ID INT = 0

SET ANSI_NULLS OFF
Select case when @ID &gt; 0 then @ID else id end as Statements, * 
From   Test_Table  
Where  ID = case when @ID &gt; 0 then @ID else ID END -- Fails. Why??


When Id=NULL is true then why Id=Id is false??

Thanks!
Have a look at remarks on MS Docs about IS NULL:


  Remarks
  To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=).
  Comparison operators return UNKNOWN when either or both arguments are NULL. 


And at SET ANSI_NULLS MS Docs:


  SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.  


Let me add some other records to your table:

INSERT INTO Test_Table 
VALUES (NULL, 'A'),(NULL,'B'),(NULL,'C'),(NULL,'D'),(1,'E'),(2,'F');


Now, your query:




SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=id;



Returns:



id | pName
-: | :----
 1 | E    
 2 | F    



And the same for your second query:




Declare @ID INT = NULL

SET ANSI_NULLS OFF
SELECT CASE WHEN @ID &gt; 0 THEN @ID ELSE id END AS Statements, * 
FROM   Test_Table  
WHERE  ID = CASE WHEN @ID &gt; 0 THEN @ID ELSE ID END
GO



Statements | id | pName
---------: | -: | :----
         1 |  1 | E    
         2 |  2 | F    



Null indicates lack of value and you must explicitly check for NULL values using IS NULL:

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id IS NULL;


dbfiddle here