The ELSE in the first example is never executed when SET ANSI_NULLS OFF.

SELECT #Temp.*,
   CASE WHEN (#Temp.S1 = #Temp.S2) OR COALESCE(#Temp.S1,#Temp.S2) IS  NULL THEN 1
        ELSE 2  END AS S3 From #Temp

>

>-- Example 1
>SET ANSI_NULLS OFF
>
>Declare @x Integer, @y INTEGER
>SET @y = 0
>SELECT @x AS S1, @y AS S2, CASE WHEN @x = @y THEN 1 WHEN @x <> @y THEN 2 ELSE NULL END AS S3
>
>-- Produces NULL 0 2
>
>-- Example 2
>Create Table #Temp (S1 Int, S2 Int)
>Insert into #Temp (S1) Values (NULL)
>Update #Temp Set S2 = 0
>Select #Temp.*,
>   CASE WHEN #Temp.S1 = #Temp.S2 THEN 1
>      WHEN #Temp.S1 <> #Temp.S2 THEN 2
>      ELSE NULL
>   END AS S3 From #Temp
>-- Produces NULL 0 NULL
>

>
>How can I make sure that the Null comparison is as in Example 1?
>
>BR