SQL-Server CONCAT case

Something like this should work as well...

SELECT concat(firstname
     ,CASE WHEN ISNULL(middlename,'') <> '' THEN ' '+middlename+'.'
        WHEN ISNULL(middlename,'') <> '' AND ISNULL(suffix,'') = '' THEN '.' 
        ELSE ' ' END
     ,lastname
     ,CASE WHEN ISNULL(suffix,'') <> '' THEN ', '+suffix END)
FROM #TB_Customer

OUTPUT:

John Jacob.Adams, St
Russel Thyrone.Peterson, pr
Anne Candice.Acola, aca
Sophia Veronika.Turner, tvs
Margaret Delafleur
Jessica Luana.Cruz
Dyrius Cruz, dc
John Adams, St

I cant see the error in you query, i know if one of the columns is null, all the others will be, but try this way:

SELECT COALESCE(c.FIRSTNAME,'') + ' ' +
        CASE WHEN COALESCE(c.MiddleName,'') = ''
                THEN ''
                ELSE c.MiddleName + '.'
            END
        + COALESCE(c.LASTNAME,'') +
        CASE WHEN COALESCE(Suffix,'') = ''
                THEN ''
                ELSE  ', ' + Suffix
             END AS CustomerName, c.*

FROM   #TB_Customer c;

@Henrik is right, '' and NULL are diferent things