'SQL Server - NULL vs blank in IF condition - ISNULL vs COALESCE

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;
nullcoalesceisnull


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jonas Metzler

Related Questions