SQL Server – NULL vs blank in IF condition – ISNULL vs COALESCE

May 6, 2022

I am expecting ELSE part to be printed in this case. But it doesn’t. It works if the input is blank ''. But for null, it fails. Even with ISNULL and COALESCE, it is same. Could you please explain the logic?

DECLARE @V_MY_VAR VARCHAR(50) = NULL;
IF ISNULL(@V_MY_VAR,'X') = 'HELLO'
    BEGIN
        PRINT 'INSIDE IF - '+ @V_MY_VAR;
    END;
ELSE
    BEGIN
        PRINT 'INSIDE ELSE - '+ @V_MY_VAR;
    END;

>Solution :

Since your variable is null, you can’t concat it with the string in your if or else clause.
You must remove it…

DECLARE @V_MY_VAR VARCHAR(50) = NULL;
IF ISNULL(@V_MY_VAR,'X') = 'HELLO'
BEGIN
    PRINT 'INSIDE IF - ';
END;
ELSE
BEGIN
    PRINT 'INSIDE ELSE - ';
END;

…or replace it by a non null value, as example using your propose ISNULL.

DECLARE @V_MY_VAR VARCHAR(50) = NULL;
IF ISNULL(@V_MY_VAR,'X') = 'HELLO'
BEGIN
    PRINT 'INSIDE IF - '+ ISNULL(@V_MY_VAR,1);
END;
ELSE
BEGIN
    PRINT 'INSIDE ELSE - '+ ISNULL(@V_MY_VAR,2);
END;

You can test this here: db<>fiddle