Unknown system variable when calling Procedure in SingleStore

Question

I've created the following procedure in SingleStore

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"

Answer 1

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 ;

Answer 2

try this query

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 ', tableName, ' ADD COLUMN ', columnName, ' LONGTEXT CHARACTER SET utf8mb4 NOT NULL');
ELSE
SET command = CONCAT('ALTER TABLE ', tableName, ' DROP COLUMN ', columnName);
END IF;

EXECUTE IMMEDIATE command;

END //
DELIMITER ;