Is not null in mysql stored procedure?

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?

Is not null in mysql stored procedure?

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)

Is not null in mysql stored procedure?

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

IS NOT NULL in MySQL?

The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

Is NULL and is not null in MySQL?

“IS NULL” is the keyword that performs the Boolean comparison. It returns true if the supplied value is NULL and false if the supplied value is not NULL. “NOT NULL” is the keyword that performs the Boolean comparison. It returns true if the supplied value is not NULL and false if the supplied value is null.

IS NOT NULL operator in SQL?

Description. The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Is NULL or is not null?

The IS NULL condition is satisfied if the column contains a null value or if the expression cannot be evaluated because it contains one or more null values. If you use the IS NOT NULL operator, the condition is satisfied when the operand is column value that is not null, or an expression that does not evaluate to null.

Tải thêm tài liệu liên quan đến bài viết Is not null in mysql stored procedure?