MySql syntax question

Question

Why does this work perfectly:

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''residents'' ORDER BY ORDINAL_POSITION;';

PREPARE STMT FROM @columnQuery;
EXECUTE STMT;

but this does not:

DECLARE TableName VARCHAR(50);
SET @TableName = 'residents';

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''' + @TableName + ''' ORDER BY ORDINAL_POSITION;';

PREPARE STMT FROM @columnQuery;
EXECUTE STMT;

and neither does this:

DECLARE TableName VARCHAR(50);
SET @TableName = 'residents';

SET @columnQuery = CONCAT('INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''', @TableName, ''' ORDER BY ORDINAL_POSITION;');

PREPARE STMT FROM @columnQuery;
EXECUTE STMT;

Neither does this:

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=? ORDER BY ORDINAL_POSITION;';

PREPARE STMT FROM @columnQuery;
EXECUTE STMT USING @TableName;

Here is the part of the sproc that is giving me issues:

CREATE PROCEDURE sp_ReturnSingleRowAsXml
(
  IN TableName VARCHAR(50)
)
BEGIN

DECLARE columnQuery VARCHAR(1000);

DROP TABLE IF EXISTS ColumnNames;

CREATE TEMPORARY TABLE ColumnNames (
  ID INT, ColumnName VARCHAR(50)
);

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''' + @TableName + ''' ORDER BY ORDINAL_POSITION;';

PREPARE STMT FROM @columnQuery;
EXECUTE STMT;

select * from ColumnNames;

END;

CALL sp_ReturnSingleRowAsXml('residents');

The select returns nothing when it should return about 40 rows. BUT, if I hard code the word 'residents' IN the @columnQuery, I get results! And yes, there is definitely a table named 'residents'.

Sometimes I get this error, sometimes I don't, depending on whether I'm using CONCAT or just manually concatenating strings:

MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Answer 1

try this:

DECLARE TableName VARCHAR(50); 
SET @TableName = 'residents'; 

SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=? ORDER BY ORDINAL_POSITION;'; 

PREPARE STMT FROM @columnQuery; 
EXECUTE STMT USING @TableName; 

Answer 2

I was able to get it to work with the following:

CREATE PROCEDURE `sp_ReturnSingleRowAsXml`( 
  IN TableName VARCHAR(50) 
)
BEGIN 

DROP TABLE IF EXISTS ColumnNames; 

CREATE TEMPORARY TABLE ColumnNames ( 
  ID INT, ColumnName VARCHAR(50) 
); 
SET @tblName = TableName;
SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=? ORDER BY ORDINAL_POSITION;'; 

PREPARE STMT FROM @columnQuery; 
EXECUTE STMT USING @tblName; 

select * from ColumnNames; 

END