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

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

Answer 2

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 coalese on each of the variables to set them to " " if they are null.

Answer 3

I agree with keithwarren, but I would always be sure to add an ORDER BY clause to the query. You can then be sure as to exactly what order the values are being concatenated in.

Also, the COALESCE to replace the NULL value with '' will effectively yield blank rows. I don't know if you want them or not, but if not just filter in the WHERE clause instead...

Finally, you appear to have a temp table including the IDs you're interested in. This table can just be included in a JOIN to filter the source table...

DELCARE @output VARCHAR(8000)
SET @output = ''

SELECT
    @output = @output + [Categories].Description + '<br/>'
FROM
    Categories
INNER JOIN
    #vals
        ON #vals.val = [Categories].ID
WHERE
   [Categories].Description IS NOT NULL
ORDER BY
   [Categories].Description