I'm getting a NULL output in a SQL Function when concatting fields

Question

I have the following function:

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)

declare @NewStatusID char(2)
declare @UserName varchar(255)
declare @Stamp datetime
declare @Notes 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

Now, that function returns exactly what I want for the Transactions that don't have any Notes in any records... For transaction that have at least one record in TransactionStatusChanges with a non-NULL Notes field, I get NULL.

I don't quite get it, since I AM checking that @Notes is not NULL before concatting it.

Any ideas?

NOTE: I'm using varchar(8000) because I can't use text inside Functions.

Answer 1

One of these is NULL

   set @output = @output + RTRIM(CAST(@Stamp AS varchar(30))) + ': ' + @NewStatusID + ' by ' + @UserName +  CHAR(13) + CHAR(10)

Also, you can make your code simpler by using ISNULL or COALESCE to handle columns which contain NULLs

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

declare @output AS varchar(8000)

select @output = ISNULL(@output, '')
        + ISNULL(RTRIM(CAST(Stamp AS varchar(30))), '<NULL>')
        + ISNULL(NewStatusID, '<NULL>') + ' by '
        + ISNULL(FirstName + ' ' + LastName, '<NULL>') + CHAR(13) + CHAR(10)
        + ISNULL('---' + Notes +  CHAR(13) + CHAR(10), '')
from TransactionStatusChanges tsc
left join Users us ON tsc.UserID = us.UserID
where TransactionID = @TransactionID ORDER BY StatusNum

RETURN @output

END