>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 >>>>