SQL Function gives NULL output while concatenating fields despite non-NULL check

Data and Databases
1

I’m developing a SQL function to retrieve history information for orders, but I’m encountering a puzzling problem with string concatenation.

CREATE FUNCTION fnRetrieveOrderHistory
(
    @OrderID int
)
RETURNS varchar(8000) AS
BEGIN

declare HistoryLog cursor for
    select StatusID, FirstName + ' ' + LastName AS UserFullName, 
           DateModified, CAST(Remarks AS varchar(8000)) AS Remarks
    from OrderLogs ol
        left join Staff st ON ol.StaffID = st.StaffID
    where OrderID = @OrderID ORDER BY OrderSequence

declare @output varchar(8000)
declare @StatusID char(2)
declare @UserFullName varchar(255) 
declare @DateModified datetime
declare @Remarks varchar(8000)

set @output = ''

OPEN HistoryLog
  FETCH NEXT FROM HistoryLog INTO @StatusID, @UserFullName, @DateModified, @Remarks
  WHILE @@FETCH_STATUS = 0
  BEGIN
    set @output = @output + RTRIM(CAST(@DateModified AS varchar(30))) + ': ' + @StatusID + ' by ' + @UserFullName + CHAR(13) + CHAR(10)
    
    IF @Remarks IS NOT NULL
    BEGIN
        set @output = @output + '---' + @Remarks + CHAR(13) + CHAR(10)
    END
    
    FETCH NEXT FROM HistoryLog INTO @StatusID, @UserFullName, @DateModified, @Remarks
  END
CLOSE HistoryLog
DEALLOCATE HistoryLog

RETURN @output
END

What confuses me is that the function operates as expected when all records have NULL remarks. However, when at least one record has a real remark instead of NULL, the function returns NULL instead of the anticipated string.

I’ve ensured that I’m verifying for NULL before concatenating, so I’m unsure of the reason behind this issue. I need to work with varchar(8000) since text types can’t be used in functions.

What might be the cause of this inconsistency?

2

This happens because of how SQL Server handles NULL values in string concatenation. Your LEFT JOIN to the Staff table can return NULL for FirstName or LastName when there’s no matching record. When you concatenate FirstName + ’ ’ + LastName and either field is NULL, the whole thing becomes NULL - that’s just how SQL Server works. Then @UserFullName gets set to NULL, and since you’re not checking for NULLs before adding it to your main string, the entire @output becomes NULL too. Easy fix: update your cursor query to handle the NULLs upfront with ISNULL or COALESCE like this: select StatusID, ISNULL(FirstName + ’ ’ + LastName, ‘Unknown User’) AS UserFullName, DateModified, CAST(Remarks AS varchar(8000)) AS Remarks. Or just add a NULL check for @UserFullName in your loop, same way you’re already doing it for @Remarks.

3

yeah, that’s sql server 101 - any NULL in a concatenation nukes the entire string. you’re checking @remarks, but @userfullname from your join is probably NULL sometimes. quick debug: print each variable before you concatenate them to see which one’s causing it.

4

Interesting issue! Does this happen every time when remarks has data? Could be hidden characters or encoding problems in your remarks field screwing up the concatenation. Try running a select on just the remarks data to see what’s actually stored. Also try trimming remarks before you concatenate.