T-SQL While Loop and concatenation

Question

I have a SQL query that is supposed to pull out a record and concat each to a string, then output that string. The important part of the query is below.

DECLARE @counter int;
SET @counter = 1;

DECLARE @tempID varchar(50);
SET @tempID = '';

DECLARE @tempCat varchar(255);
SET @tempCat = '';

DECLARE @tempCatString varchar(5000);
SET @tempCatString = '';

WHILE @counter <= @tempCount
BEGIN

    SET @tempID = (
    SELECT [Val]
    FROM #vals
    WHERE [ID] = @counter);

    SET @tempCat = (SELECT [Description] FROM [Categories] WHERE [ID] = @tempID);
    print @tempCat;

    SET @tempCatString = @tempCatString + '<br/>' + @tempCat;
    SET @counter = @counter + 1;

END

When the script runs, @tempCatString outputs as null while @tempCat always outputs correctly. Is there some reason that concatenation won't work inside a While loop? That seems wrong, since incrementing @counter works perfectly. So is there something else I'm missing?

Answer 1

Looks like it should work but for somereason it seems to think @tempCatString is null which is why you are always getting a null value as nullconcatenated to anything else is still null. Suggest you try with COALESCE() on each of the variables to set them to " " if they are null.

Answer 2

this would be more efficient....

select @tempCatString = @tempCatString + Coalesce(Description,'') + '<br/>' from Categories...

select @fn

also look at concat_null_yields_null as an option to fix your concatenation issue, although I would avoid that route