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

mysql, stored-procedures

Solution

`@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 ;

Problem

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?

Original source