Mysql stored procedure set/declare variable issue

Question

I am working in Mysql and I would like to create a stored procedure to insert in database if user does not have more than 10 hours in table rental.

Here is my code :

CREATE PROCEDURE insert_rental(in user_idInsert INTEGER, in fields_idInsert INTEGER, in startDateTimeInsert DATETIME, in endDateTimeInsert DATETIME, in monthDate INTEGER, in yearDate INTEGER, in user_role INTEGER) 
BEGIN  
DECLARE user_totalHours FLOAT(23,19); 

SELECT Sum(time_to_sec(TIMEDIFF(endDateTimeInsert,startDateTimeInsert))/3600)  
INTO user_totalHours  
FROM `rental`  
WHERE MONTH(startDateTime)=monthDate  
AND YEAR(startDateTime)=yearDate  
AND user_id = user_idInsert; 

IF user_role=2 && user_totalHours<10 THEN  
        INSERT INTO rental(user_id, field_id ,startDateTime, endDateTime)  
        VALUES(user_idInsert, fields_idInsert, startDateTimeInsert, endDateTimeInsert); 
    ELSEIF user_role!=2 THEN  
        INSERT INTO rental(user_id, field_id, startDateTime, endDateTime)  
        VALUES(user_idInsert, fields_idInsert, startDateTimeInsert, endDateTimeInsert); 
END IF; 
END

I have also tried this :

SET @user_totalHours = SELECT Sum(time_to_sec(TIMEDIFF(endDateTimeInsert,startDateTimeInsert))/3600)  
FROM `rental`  
WHERE MONTH(startDateTime)=monthDate  
AND YEAR(startDateTime)=yearDate  
AND user_id = user_idInsert;

But variable user_totalHours is always NULL

Thanks for help.

Your queries seem wrong, it is INSERT INTO not SELECT INTO and use UPDATE before using SET if you are trying to update a row. — programmingandroid
– programmingandroid, Commented Mar 18, 2017 at 15:57
I want to store in variable : user_totalHours (float) and if this float is under 10, do INSERT INTO rental — AdrienG
– AdrienG, Commented Mar 18, 2017 at 16:00

AdrienG · Accepted Answer · 2017-03-18 16:29:33Z

I solved this problem, it was that if there is nothing in rental for a month like january, null is the value, so I've changed my else if statement :

   DECLARE user_totalHours FLOAT DEFAULT 0.0; 

SELECT Sum(time_to_sec(TIMEDIFF(endDateTime,startDateTime))/3600)  
INTO user_totalHours  
FROM `rental`  
WHERE MONTH(startDateTime)=monthDate  
AND YEAR(startDateTime)=yearDate  
AND user_id = user_idInsert; 

    IF (user_role!=2) THEN
            INSERT INTO rental(user_id, fields_id ,startDateTime, endDateTime)  
            VALUES(user_idInsert, fields_idInsert, startDateTimeInsert, endDateTimeInsert); 
    ELSEIF (user_totalHours<10 || user_totalHours IS NULL) THEN  
        INSERT INTO rental(user_id, fields_id, startDateTime, endDateTime)  
        VALUES(user_idInsert, fields_idInsert, startDateTimeInsert, endDateTimeInsert);
        ELSE SELECT user_totalHours;
    END IF;

Collectives™ on Stack Overflow

Mysql stored procedure set/declare variable issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related