Using a variable as a column name in a stored procedure?

Question

Here I wrote a mysql procedure which select all text type field of specified database 'wp'

and I want to update every text type field row appending extra string via CONCAT function.

after I call test2 , error shows:

Query : call test2()

Error Code : 1146
Table 'wp._tbl' doesn't exist

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000
---------------------------------------------------

mysql regards _tbl as a string but not a variable.

So can I correct this?

code:

DELIMITER $$
USE `wp`$$
DROP PROCEDURE IF EXISTS `test2`$$
CREATE
    PROCEDURE `test2`()
    BEGIN
    DECLARE _tbl VARCHAR(100);
    DECLARE _cl VARCHAR(100);
    DECLARE notFound INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT table_name,column_name FROM information_schema.columns WHERE table_schema = 'wp' AND data_type ='text';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET notFound =1;
    OPEN cur;
        WHILE notFound = 0 DO
            FETCH cur INTO _tbl,_cl;
            UPDATE _tbl SET _cl = CONCAT(_cl,'extra string goes here.....');
            IF NOT noFound THEN SET notFound = 0;
            END IF;
        END WHILE;
    CLOSE cur;
    END$$

DELIMITER ;

SQL does not support a variable in that position -- only an identifier. The only way this is possible is with ["dynamic SQL"](http://stackoverflow.com/questions/1471570/dynamic-mysql-with-local-variables), but that starts to get *really messy*. — , Sep 10 '11 at 05:36

score 3 · Accepted Answer · edited May 23 '17 at 12:04

3

You'll need to use prepared statements. Also see How To have Dynamic SQL in MySQL Stored Procedure

edited May 23 '17 at 12:04

Community

1
1

answered Sep 10 '11 at 05:34

Conrad Frix

51,984
12
96
155

Using a variable as a column name in a stored procedure?

1 Answers1