TSQL COALESCE v ISNULL

The COALESCE function accepts a list of expressions as input and returns the first that is not NULL. COALESCE(<expr1>, <expr2>…<exprn>). The ISNULL function replaces  expression1 with expression2 if expression1 is NULL. The difference is that ISNULL uses the dataformat of expression1 for the replacement whereas COALESCE uses the dataformat of the first not NULL expression:

DECLARE
    @x as varchar(5) = NULL,
    @y as varchar(10) = '0123456789',
    @z as varchar(15) = 'ABCDEGHIJKLMNOP';

select
    COALESCE    (@X,@Y) as Cxy,
    ISNULL        (@x,@y)    as Ixy,
    COALESCE    (@X,@z) as Cxz,
    ISNULL        (@x,@z)    as Ixz
   
--Cxy                Ixy        Cxz                           Ixz
--0123456789    01234    ABCDEGHIJKLMNOP    ABCDE

set @y = NULL;

select
    COALESCE    (@X,@Y,@z) as Cxyz,
    ISNULL(ISNULL(@x,@y),@z)    as Ixyz
--Cxyz                          Ixyz
--ABCDEGHIJKLMNOP    ABCDE

Till Next Time