Working With NULLS

In SQL Server one of the interesting to deal with is NULLS while programming in T-SQL and also design of tables. Very recently i ran into an issue with NULLS, the scenario was in the context of T-SQL. The business rules were such that I had to compare values, so the SQL was constructed. Once the stored procedure was complete, it was time to unit test the stored procedure. On executing the stored procedure I was not getting the desired results, the expected the result was there were rows that should have got updated in the table. On debugging the sql, the problem area was comparing the two numeric values, the condition was like CurrentAmount <> InputAmount. Here there were 6 rows in the table which had NULLs in the currentamount and the other 10 rows which had values, when comparing NULL to a value the result is an Undetermined state this caused no rows to be updated. The expectation was 16 rows would be updated, in fact after a little bit of examining, the ISNULL function was used on the arguments on both sides of the condition. The check was modified to ISNULL(CurrentAmount,0) <> ISNULL(InputAmount), once this was done, I got the desired results. While working with NULLs it would be good to keep in mind the undetermined state while comparision.
The above situation arises when:
When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:
When ANSI_NULLS IS OFF, the above behavior mentioned is not applicable.