Use an input parameter as a keyword in a stored procedure

Question

MySQL Workbench is hitting me with the following error

The object's DDL statement contains syntax errors. You cannot modify this object until you fix the errors.

as well as the hint

"SELECT" is not valid at this position for this server version, expecting: '(', WITH

for the following code structure, where I'm trying to define a procedure that takes an argument called time_period which is used as SQL time keyword like MINUTE, HOUR, WEEK, etc, and a second argument period_count that will multiply that time period.

DELIMITER //
CREATE PROCEDURE getStuffFromPast(
  IN time_period VARCHAR(10),
  IN period_count INT(1)
)
BEGIN
  SELECT * 
  FROM table_A
  INNER JOIN 
    (SELECT x, y, record_date
     FROM table_B
     WHERE (record_date > (NOW() - INTERVAL period_count time_period))
    ) AS B
  ON table_A.x = B.x
    AND table_A.y = B.y;
END//
DELIIMTER ;

I know it's not a delimiter issue because if I alter the code to

DELIMITER //
CREATE PROCEDURE getStuffFromPast(
  IN time_period VARCHAR(10),
  IN period_count INT(1)
)
BEGIN
  SELECT * 
  FROM table_A
  INNER JOIN 
    (SELECT x, y, record_date
     FROM table_B
     WHERE (record_date > (NOW() - INTERVAL period_count HOUR))
    ) AS B
  ON table_A.x = B.x
    AND table_A.y = B.y;
  SELECT time_period;
END//
DELIIMTER ;

I get the correct first output followed by the correct second output. So I'm pretty sure my problem is syntactically using input parameters as keywords/data types in the procedure.

If there's a more elegant way to do what I want—call this procedure from python client like

def execute_sql(query):
   # Handles DB interactions

def get_data_from_past(time_period: str, count: int):
   sql_statement = f'call getStuffFromPast({time_period}, {count})'
   results = execute_sql(sql_statement)

Actually... as typing, I realized we could convert the time argument in the python method to the fundamental time unit we want to deal with in the stored procedure; but I'd still like to know if there's a way to do what I want, in the above stored procedure.

Answer 1

Your Query is essentially ok,but you have to use stmt

DELIMITER //
CREATE PROCEDURE getStuffFromPast(
  IN time_period VARCHAR(10),
  IN period_count INT(1)
)
BEGIN
  IF time_period NOT IN   ('MICROSECOND','SECOND','MINUTE','HOUR','DAY'
    ,'WEEK','MONTH','QUARTER','YEARSECOND_MICROSECOND','MINUTE_MICROSECOND'
    ,'MINUTE_SECOND','HOUR_MICROSECOND','HOUR_SECOND','HOUR_MINUTE'
    ,'DAY_MICROSECOND','DAY_SECOND','DAY_MINUTE','DAY_HOUR','YEAR_MONTH') THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Time period is not valid';
  END IF;
  SET @sql = 'SELECT * ';
  SET @sql = CONCAT(@sql,'FROM table_A ');
  SET @sql = CONCAT(@sql,'INNER JOIN ');
  SET @sql = CONCAT(@sql,'(SELECT x, y, record_date ');
  SET @sql = CONCAT(@sql,'FROM table_B ');
  SET @sql = CONCAT(@sql,'WHERE (record_date > (NOW() - INTERVAL ',period_count,' ',time_period,')) ');
  SET @sql = CONCAT(@sql,') AS B ');
  SET @sql = CONCAT(@sql,'ON table_A.x = B.x ');
  SET @sql = CONCAT(@sql,'AND table_A.y = B.y;');

  PREPARE stmt from @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;