Dynamic procedure to change Column name

Question

I need to create a dynamic procedure so that whenever I need to rename a column of a table I pass the desired parameters and it executes without errors. So the verification of the IF.

The error generated by this code is as follows:

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 'IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = 'People'' at line 1

DROP PROCEDURE IF EXISTS `change_column_name`;

DELIMITER ;;
CREATE PROCEDURE `change_column_name`(IN tableName VARCHAR(100), IN columnName VARCHAR(100), IN newColumnName VARCHAR(100), IN columnType VARCHAR(20), IN defaultValue VARCHAR(100))
BEGIN
    SET @query = CONCAT('IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = \'', tableName, '\' AND column_name = \'', columnName, '\') THEN
                                ALTER TABLE \'', tableName, '\' CHANGE COLUMN \'', columnName, '\' \'', newColumnName, '\' ', columnType, ' DEFAULT ', defaultValue, ';
                        END IF;');

    PREPARE stmt1 FROM @query;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END ;;
DELIMITER ;

CALL `change_column_name`('People', 'Nme', 'Name', 'VARCHAR(50)', 'NULL');

@RaymondNijland Nop. If you leave the same code and remove the parameters, change everything to hard coded it works. — Luiz Negrini, Dec 22 '17 at 11:52
The way you used it the concat function is SQL server syntax search on it — Raymond Nijland, Dec 22 '17 at 11:57
@RaymondNijland Ok, so why does it work if you remove the parameters and leave hardcoded? — Luiz Negrini, Dec 22 '17 at 11:59
Don't believe that the generated string variable @qeury contains simply a invalid MySQL syntax. You need to seperate the SELECT and ALTER table queries for it to work In MySQL.. i cant write a answer right now because iam on a phone right now — Raymond Nijland, Dec 22 '17 at 12:08
making a select now in this @query, the return is exactly the query I want. I can not see the problem. Can you explain more? — Luiz Negrini, Dec 22 '17 at 12:11
This topic https://stackoverflow.com/questions/14381895/mysql-add-column-if-not-exist looks like it's comparable with yours — Raymond Nijland, Dec 22 '17 at 12:17

Luiz Negrini · Answer 1 · 2017-12-22T12:24:07.647

Solved, the way the steps were made were wrong.

DROP PROCEDURE IF EXISTS `change_column_name`;

DELIMITER ;;
CREATE PROCEDURE `change_column_name`(IN tableName VARCHAR(100), IN columnName VARCHAR(100), IN newColumnName VARCHAR(100), IN columnType VARCHAR(20), IN defaultValue VARCHAR(100))
BEGIN
    IF EXISTS (SELECT * FROM information_schema.COLUMNS WHERE table_name = tableName AND column_name = columnName) THEN
        SET @query = CONCAT('ALTER TABLE `', tableName, '` CHANGE COLUMN `', columnName, '` `', newColumnName, '` ', columnType, ' DEFAULT ', defaultValue, ';');
        PREPARE stmt1 FROM @query;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;    
END ;;
DELIMITER ;

CALL `change_column_name`('People', 'Nme', 'Name', 'VARCHAR(50)', 'NULL');

Dynamic procedure to change Column name

1 Answers1