NULLIF()

NULLIF() returns NULL if the two parameters provided are equal; otherwise, the value of the first parameter is returned.  Seems a little odd and not very useful, but it is a great way of ensuring that empty strings are always returned as NULLS.  

For example, the expression:

nullif(@variable1,'')

will never return an empty string, it will return either a NULL value or a string with at least one character present.  Also,  SQL ignores trailing spaces when comparing strings, so even if the string isn't empty but it contains all spaces, it will still return NULL.

select nullif('     ','') 
 
----
NULL

NULLIF() can be a very useful function to employ.  Consider it when you need to replace default values other than just NULL when using ISNULL() or COALESCE() expressions.

http://msdn.microsoft.com/en-us/library/aa276840(v=sql.80).aspx