MySql syntax question regarding CONCAT and strings

Question

Environment is MySql 5.1.5.

This is a snippet from a larger stored procedure. Assume the variables are properly declared and set before this code is reached.

When I run the following loop, something seems to be failing in the CONCAT. @columnName and @xmlQuery are both VARCHARs. When I "SELECT @xmlQuery" at the end of the procedure, it is {null}.

If I simply replace:

SET @xmlQuery = CONCAT(@xmlQuery, @columnName);

with:

SET @xmlQuery = CONCAT(@xmlQuery, 'test');

then I get a nice string back like:

select xml_tag('result',null,null,concat(testtesttesttesttesttest

as one would expect.

WHY doesn't the CONCAT work with the local VARCHAR variable?

SET @xmlQuery = 'select xml_tag(''result'',null,null,concat(';

SET @columnCount = (SELECT COUNT(*) FROM ColumnNames);

WHILE (@rowIndex <= @columnCount) DO

  SELECT @columnName = ColumnName FROM ColumnNames WHERE ID = @rowIndex;

  SET @xmlQuery = CONCAT(@xmlQuery, @columnName);

  SET @rowIndex = @rowIndex + 1;

END WHILE;

Answer 1

At some point in the while loop @columnName must be set to NULL (or left unset throughout) and then used in the assignment-to-@xmlQuery concat. If one of the concatenated values is NULL, the whole string becomes NULL. For example the following returns NULL:

SET @xmlQuery = 'select xml_tag(''result'',null,null,concat(';
select concat( @xmlQuery, null );

I'd examine the values of the column name variable in the loop - is there always a value for every ID value between the initial value of @rowIndex and @columnCount? (We can't see above what the initial value of the row index is.) Does the variable actually get set?

EDIT

It's probably that you need a := assignment in the loop:

SELECT @columnName := ColumnName FROM ColumnNames WHERE ID = @rowIndex;

Answer 2

The problem turned out to be a conflict between the local variable @columnName and the column ColumnName in my temporary table.