In Transact-SQL, system-generated NULLs, such as those that result from a system function like convert, behave differently than user-assigned NULLs. For example, in the following statement, a not equals comparison of the user-provided NULL and 1 returns true:
if (1 != NULL) print "yes" else print "no"
yes
The same comparison with a system-generated NULL returns unknown:
if (1 != convert(integer, NULL)) print "yes" else print "no"
no
For more consistent behavior, enable set ansinull (set to on), so both system-generated and user-provided NULLs cause the comparison to return unknown.
