I keep getting an error when I run this on mysql5.7 What am I doing wrong. I basically just want to add a column if the column doesnt already exist
DROP PROCEDURE IF EXISTS ALIASCOLUMN;
DELIMITER //
CREATE PROCEDURE ALIASCOLUMN()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255);
END //
DELIMITER;
CALL ALIASCOLUMN();
DROP PROCEDURE ALIASCOLUMN;
Use the following in a stored procedure:
IF NOT EXISTS( SELECT *
FROM 'tablename'
WHERE table_name = 'tablename'
AND table_schema = 'db_name'
AND column_name = 'columnname') THEN
ALTER TABLE `human_api_procedure` ADD `alias` varchar(255) ;
END IF;
Check this link. It may help you. :)
DDL statements within a procedure have to be executed as dynamic SQL. (I think that's true, I'd need to check the docs to verify.)
Also, identifiers (table names, column names) should not be enclosed in single quotes. Single quotes are delimiters for string literals.
I'd expect this statement to fail with a syntax error:
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255)
This
ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)
^ ^ ^ ^
or this
ALTER TABLE `human_api_procedure` ADD COLUMN `alias` varchar(255)
^ ^ ^ ^
would be valid syntax. We could also use double quotes around identifiers, if ANSI_QUOTES is included in sql_mode.
To execute an ALTER TABLE statement in the context of a MySQL stored program, we could do something like this:
DELIMITER $$
CREATE PROCEDURE aliascolumn()
BEGIN
SET @sql = 'ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
MySQL permits routines to contain DDL statements, such as CREATE and DROP.
https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
The pattern demonstrated above, using the PREPARE/EXECUTE/DEALLOCATE would allow execution of for dynamic SQL, not just static SQL text.
