Thursday, November 6, 2008

Difference between NULL and (Empty or ' ')

DECLARE @A VARCHAR(10)
DECLARE @B VARCHAR(10)
DECLARE @C VARCHAR(10)

BEGIN
 --Step:1
        SELECT ISNULL(@A , ' ')  -- RETURNS ' '
        SELECT ISNULL(@A , NULL) -- RETURNS NULL
        SELECT ISNULL(@A , @B) -- RETURNS NULL
        
        --Step:2
        SET @A = '' --Since we have assigned some value which is not null
        SELECT ISNULL(@A , ' ')  -- RETURNS ''
        SELECT ISNULL(@A , NULL) -- RETURNS ''
        SELECT ISNULL(@A , @B) -- RETURNS ''
        
        --Step:3
 SET @A = NULL--Which is same as the Step:1
        SELECT ISNULL(@A , ' ')  -- RETURNS ''
        SELECT ISNULL(@A , NULL) -- RETURNS NULL
        SELECT ISNULL(@A , @B) -- RETURNS NULL
        
        --Step:4
        SET @B = ' '
        SELECT ISNULL(@B , ' ')  -- RETURNS ' '
        SELECT ISNULL(@B , NULL) -- RETURNS ' '
        SELECT ISNULL(@B , @C)   -- RETURNS ' '
        SELECT ISNULL(@C , @B)   -- RETURNS ' '
        SELECT ISNULL(@B , @A)   -- RETURNS ' '
        
        --Step:5
        SET @A = '1'
        SELECT ISNULL(@B , @A) -- RETURNS ' '
        SELECT ISNULL(@A , @B) -- RETURNS 1
        SELECT ISNULL(@C , @A) -- RETURNS 1
END

No comments: