Unknown system variable when calling procedure

Help
1

Hey guys. I’ve created the following procedure:

DELIMITER //
CREATE OR REPLACE PROCEDURE updateColumnModelName(tableName TEXT, columnName TEXT) AS
  DECLARE has_column INT DEFAULT 0;
  DECLARE command TEXT;
  BEGIN
    SELECT EXISTS (
     SELECT *
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = tableName
     AND COLUMN_NAME = columnName
    ) INTO has_column;

     IF has_column THEN
       SET command = CONCAT('ALTER TABLE ', table_name, ' ADD COLUMN ', column_name, ' LONGTEXT CHARACTER SET utf8mb4 NOT NULL');
     ELSE
       SET command = CONCAT('ALTER TABLE ', table_name, ' DROP COLUMN ', column_name);
     END IF;
    
     EXECUTE IMMEDIATE command;

   END //
DELIMITER ;

Procedure is created with no problems, but when I call it by

CALL updateColumnModelName("Transcription", "ModelName");

I receive the following error:

ERROR 1193 ER_UNKNOWN_SYSTEM_VARIABLE: Unhandled exception Type: ER_UNKNOWN_SYSTEM_VARIABLE (1193) Message: Unknown system variable ‘comand’ Callstack: #0 Line 13 in example_db.updateColumnModelName

I tried to use a different approach with

DECLARE dynamic_sql TEXT;  
....  
SET @stmt = command;     
PREPARE stmt FROM @stmt;      
EXECUTE stmt;      
DEALLOCATE PREPARE stmt;

But received the following error in this case:

ERROR 1149 ER_SYNTAX_ERROR: line 20, syntax error at or near "stmt"

Any advice on how to solve that? My intention is dynamically check on a table for some column presence. If it exists I want to perform a alter table.

2

Anyone? Please? Being mad here with this. :melting_face: :smiling_face_with_tear:

3

You do not need SET to assign values to a variable inside the stored proc.
Instead of
SET command = CONCAT('ALTER TABLE ', table_name, ' DROP COLUMN ', column_name);

use
command = CONCAT('ALTER TABLE ', table_name, ' DROP COLUMN ', column_name);

4

Hey Tjain, thaks for the tip. Actually I managed too solve the problem. It was related to the variable names since I was making confusion with the values passed by reference to the function.

Here is the workable version in case of anyone needs.

DELIMITER //

CREATE OR REPLACE PROCEDURE updateColumnModelName(tableName TEXT, columnName TEXT) AS
DECLARE has_column INT DEFAULT 0;

BEGIN
  SELECT EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = tableName
    AND COLUMN_NAME = columnName
  ) INTO has_column;

    IF NOT has_column THEN
      EXECUTE IMMEDIATE CONCAT('ALTER TABLE ', tableName, ' ADD COLUMN ', columnName, ' LONGTEXT CHARACTER SET utf8mb4 NOT NULL');
    END IF;
    
END //

DELIMITER ;