SQL Function gives NULL output when trying to concatenate fields with non-null values

Data and Databases
1

I’m having an issue with my SQL function that generates a status log by combining various fields. It works fine when none of the records have any content in the Notes column. However, when just one record has some Notes, the function returns NULL instead of the expected log string.

Below is my function code:

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

declare StatusLogCursor cursor for
    select NewStatusID, FirstName + ' ' + LastName AS UserFullName, 
           CreatedOn, CAST(NoteContent AS varchar(8000)) AS NoteContent
    from StatusHistory sh
        left join Users u ON sh.UserID = u.UserID
    where TransactionID = @TransactionID ORDER BY StatusOrder

declare @resultLog varchar(8000)
declare @NewStatusID char(2)
declare @UserFullName varchar(255)
declare @CreatedOn datetime
declare @NoteContent varchar(8000)

set @resultLog = ''

OPEN StatusLogCursor
  FETCH NEXT FROM StatusLogCursor INTO @NewStatusID, @UserFullName, @CreatedOn, @NoteContent
  WHILE @@FETCH_STATUS = 0
  BEGIN

    set @resultLog = @resultLog + RTRIM(CAST(@CreatedOn AS varchar(30))) + ': ' + @NewStatusID + ' by ' + @UserFullName + CHAR(13) + CHAR(10)

    IF @NoteContent IS NOT NULL
    BEGIN
        set @resultLog = @resultLog + '---' + @NoteContent + CHAR(13) + CHAR(10)
    END

    FETCH NEXT FROM StatusLogCursor INTO @NewStatusID, @UserFullName, @CreatedOn, @NoteContent
  END
CLOSE StatusLogCursor
DEALLOCATE StatusLogCursor

RETURN @resultLog

END

I ensure that NoteContent is not NULL before adding it to the result string, so I’m confused why the output is NULL when there are non-NULL values for Notes. Could anyone help me troubleshoot this problem?

2

you’re hitting the classic sql null concatenation issue. when you concatenate with @resultLog = @resultLog + something and any part is null, everything becomes null. wrap your variables with isnull() or coalesce() like isnull(@UserFullName, '') to fix the null handling.

3

yep, that’s def a common issue! if any part of your concatenation is null, the whole result becomes null. maybe double-check if @UserFullName is null from the left join. running the select part directly could help you see what’s going on.

4

This happens because SQL Server’s concatenation turns everything NULL when any part is NULL. You’re checking @NoteContent for NULL, but @UserFullName can also be NULL from your LEFT JOIN when there’s no matching user record. I hit this same issue in a reporting function - missing users killed the entire output. Fix it by wrapping each variable with ISNULL() or COALESCE(). Change your concatenation to: set @resultLog = @resultLog + RTRIM(CAST(@CreatedOn AS varchar(30))) + ': ' + @NewStatusID + ' by ' + ISNULL(@UserFullName, 'Unknown User') + CHAR(13) + CHAR(10). This replaces NULL values with defaults so your result doesn’t get nuked.