MySql syntax question regarding CONCAT and strings

Environment is MySql 5.1.5.

This is a snippet from a larger stor开发者_StackOverflow中文版ed 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;

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