Getting NULL from SQL function when concatenating fields containing values

Data and Databases
1

I’ve designed a SQL function that compiles transaction status records into a single output string. The odd part is that it returns the expected results when there are no notes present, but it outputs NULL if any notes exist.

CREATE FUNCTION fGetTransactionStatusLog
(
    @TransactionID int
)
RETURNS varchar(8000) AS
BEGIN

declare StatusChanges cursor for
    select NewStatusID, FirstName + ' ' + LastName AS UserName, Stamp, CAST(Notes AS varchar(8000)) AS Notes
        from TransactionStatusChanges tsc
        left join Users us ON tsc.UserID = us.UserID
        where TransactionID = @TransactionID ORDER BY StatusNum

declare @output varchar(8000)

set @output = ''

OPEN StatusChanges
FETCH NEXT FROM StatusChanges INTO @NewStatusID, @UserName, @Stamp, @Notes
WHILE @@FETCH_STATUS = 0
BEGIN
    set @output = @output + RTRIM(CAST(@Stamp AS varchar(30))) + ': ' + @NewStatusID + ' by ' + @UserName + CHAR(13) + CHAR(10)
    
    IF @Notes IS NOT NULL
    BEGIN
        set @output = @output + '---' + @Notes + CHAR(13) + CHAR(10)
    END
    
    FETCH NEXT FROM StatusChanges INTO @NewStatusID, @UserName, @Stamp, @Notes
END
CLOSE StatusChanges
DEALLOCATE StatusChanges

RETURN @output
END

I examine the Notes field for NULL values before including it in the result, so I’m puzzled why the overall function yields NULL when notes are present. I am constrained to using varchar(8000) due to the limitations on text types in functions. Any advice on what could be happening?

2

interesting issue! have you checked if @NewStatusID is null too? concatenating with nulls will mess up the whole string. what data types are you working with in the original table? could be some implicit conversion weirdness happening.

3

ah, i see what’s happening - sql server returns null for the entire concatenation if any part is null. your username field is probably null from that left join. try wrapping @username with isnull() like isnull(@UserName, 'unknown') or use coalesce.

4

You’ve got a classic NULL propagation problem with SQL Server’s + operator. When any part of your concatenation is NULL, the whole thing becomes NULL - doesn’t matter that you’re checking @Notes. You’re not checking your other variables. @UserName can easily be NULL if that left join to Users doesn’t find matching FirstName or LastName values. @NewStatusID might be NULL from your source table too. Same goes for @Stamp. Fix is simple - wrap everything in ISNULL or COALESCE before concatenating. Use ISNULL(@UserName, ‘’) instead of just @UserName. This converts NULLs to empty strings so your concatenation won’t blow up.