CODEWITHSUNDEEP
mysqlstored-procedures
AdrienG
AdrienG

Reputation: 57

Mysql stored procedure set/declare variable issue

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.

Upvotes: 0

Views: 1772

Answers (1)

AdrienG
AdrienG

Reputation: 57

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;

Upvotes: 1

Related Questions