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?

I like to use `COALESCE` for replacing nulls. COALESCE(@last_run_time, Date_Sub(NOW(), INTERVAL 1 DAY)); http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce — actkatiemacias, Aug 28 '13 at 23:23
Thank you. Apparently the problem is that I am not using the variables correctly. — Jaylen, Aug 28 '13 at 23:41

wchiquito · Accepted Answer · 2013-08-29T00:20:13.987

33

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

edited Aug 29 '13 at 00:20

answered Aug 28 '13 at 23:16

wchiquito

16,177
2
34
45

then how can I make the first 3 variables Global? as I need to call them later in my code? – Jaylen Aug 28 '13 at 23:22
define them before calling the stored proc, so they have a scope outside the proc. – Bohemian Aug 28 '13 at 23:32
I need to define them in the procedure because I only need to use with within BEGIN....END. But need to know what is the correct way of using them. – Jaylen Aug 28 '13 at 23:35
I do not know you're trying to do and do not understand what you mean when you say you call them later in your code. The user-defined variables are session-specific, we can say that "global" to the session. – wchiquito Aug 28 '13 at 23:44
What I mean is that I want to define a variable on the top from a select statement. If there is noting found in the database then make that variable = yesterday. later I want to insert the value of those 3 variables into a different table. so How can I properly set the variable? thanks – Jaylen Aug 28 '13 at 23:52
Everything within the stored procedure? – wchiquito Aug 28 '13 at 23:56
@wchiquito yes everything in with int the procedure. – Jaylen Aug 28 '13 at 23:58
In that case, you do not need user-defined variables, keep all local variables in the stored procedure. – wchiquito Aug 29 '13 at 00:04
Can you please show me how? this is the first time I try to use variables. I never ever wrote a procedure in MySQL before – Jaylen Aug 29 '13 at 00:10
Update my answer with a small example. – wchiquito Aug 29 '13 at 00:21

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

1 Answers1

Linked