How to check if a variable is NULL, then set it with a MySQL stored procedure?

Question

I have a MySQL stored procedure where I find the max value from a table.

If there is no value I want to set the variable to yesterday's date.

DECLARE current_procedure_name CHAR(60) DEFAULT 'accounts_general'; DECLARE last_run_time datetime DEFAULT NULL; DECLARE current_run_time datetime DEFAULT NOW();  -- Define the last run time SET last_run_time := (SELECT MAX(runtime)  FROM dynamo.runtimes WHERE procedure_name = @current_procedure_name);  -- if there is no last run time found then use yesterday as starting point IF(@last_run_time IS NULL) THEN     SET last_run_time := DATE_SUB( NOW(), INTERVAL 1 DAY); END IF;  SELECT @last_run_time; 

The problem is that @last_run_time is always NULL.

The following code is not being executed for some reason

IF(last_run_time IS NULL) THEN     SET last_run_time := DATE_SUB( NOW(), INTERVAL 1 DAY); END IF; 

How can I set the variable @last_run_time correctly?

Answer 1

@last_run_time is a 9.4. User-Defined Variables and last_run_time datetime one 13.6.4.1. Local Variable DECLARE Syntax, are different variables.

Try: SELECT last_run_time;

UPDATE

Example:

/* CODE FOR DEMONSTRATION PURPOSES */ DELIMITER $$  CREATE PROCEDURE `sp_test`() BEGIN     DECLARE current_procedure_name CHAR(60) DEFAULT 'accounts_general';     DECLARE last_run_time DATETIME DEFAULT NULL;     DECLARE current_run_time DATETIME DEFAULT NOW();      -- Define the last run time     SET last_run_time := (SELECT MAX(runtime) FROM dynamo.runtimes WHERE procedure_name = current_procedure_name);      -- if there is no last run time found then use yesterday as starting point     IF(last_run_time IS NULL) THEN         SET last_run_time := DATE_SUB(NOW(), INTERVAL 1 DAY);     END IF;      SELECT last_run_time;      -- Insert variables in table2     INSERT INTO table2 (col0, col1, col2) VALUES (current_procedure_name, last_run_time, current_run_time); END$$  DELIMITER ;