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

ISNULL()

The ISNULL  function in SQL Server is used to substitute alternate value if the one being checked is “NULL”. 
Take the following example.

DECLARE @var1 VARCHAR(50) 
DECLARE @var2 VARCHAR(5) 

SET @var1 = 'Hello'
SET @var2= NULL

SELECT ISNULL(@var2,@var1)

Your output is Hello

Now try substituting @var1 with a bigger sentence

SET @var1 = 'Hello World Its great to be here'

Now your output is just Hello


This is because the ISNULL funnction type casts var2 to var1
Try the following, it will resolve the issue

SELECT ISNULL(CONVERT(VARCHAR(50), @var2),@var1)

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