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?
asked Aug 28, 2013 at 23:06
2
@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 ;
answered Aug 28, 2013 at 23:16
wchiquitowchiquito
15.8k2 gold badges33 silver badges45 bronze badges
10
To check for NULL or empty variable, use the IF condition. Let us create a stored procedure −
mysql> delimiter // mysql> create procedure checkingForNullDemo[Name varchar[20]] begin if Name is NULL OR Name='' then select 'Adam Smith'; else select Name; end if ; end // Query OK, 0 rows affected [0.00 sec] mysql> delimiter ;
Case 1
When NULL is passed. Call the stored procedure using call command
mysql> call checkingForNullDemo[NULL];
This will produce the following output −
+------------+ | Adam Smith | +------------+ | Adam Smith | +------------+ 1 row in set [0.00 sec] Query OK, 0 rows affected, 1 warning [0.00 sec]
Case 2
When a value is passed. Call the stored procedure using the call command
mysql> call checkingForNullDemo['John Doe'];
This will produce the following output −
+----------+ | Name | +----------+ | John Doe | +----------+ 1 row in set [0.00 sec] Query OK, 0 rows affected [0.00 sec]
Updated on 25-Feb-2020 13:02:51
- Related Questions & Answers
- Check whether a field is empty or null in MySQL?
- In MySQL stored procedures, how to check if a local variable is null?
- Set a custom value for NULL or empty values in MySQL
- Check for NULL or NOT NULL values in a column in MySQL
- Checking for Null or Empty in Java.
- How to check if field is null or empty in MySQL?
- Conditional WHERE clause in MySQL stored procedure to set a custom value for NULL values
- How do I check if a column is empty or null in MySQL?
- MySQL “not a variable or NEW pseudo-variable” message. What is this error in my Stored Procedure?
- How to check whether a stored procedure exist in MySQL?
- Stored procedure using variable in LIMIT expression?
- How can a MySQL stored procedure call another MySQL stored procedure inside it?
- Java Program to Check if a String is Empty or Null
- How to use FOR LOOP in MySQL Stored Procedure?
- Set conditions in a MySQL stored procedure