How to use a case-when statement in a mysql stored procedure?

Question

I want to set the session_id automatically using the request_time parameter so i opted for a mysql stored procedure that contains a case statement.Here goes.

create procedure upd_userinput(in  request_time timestamp, out user_session_id int) 

begin 

update user_input;

case request_time

when time(request_time) < '9:15:00' && time(request_time) > '8:15:00' 
then set user_session_id = 1; 

when  time(request_time)< '10:15:00' && time(request_time) > '11:15:00' 
then set user_session_id =2; 

end case; 
end
//

However i get a 1064 error on enter after //. I have checked the mysql documentation i think the case syntax is correct.

Help please.

Jebel

Answer 1

delimiter //

create procedure upd_userinput(in request_time TIMESTAMP,out time TIME, out user_session_id INT)     

begin      

set time = time(request_time);    

case  
when time < '09:15:00' && time > '08:15:00' then      
set user_session_id = 1; 

when time < '10:15:00' && time > '11:15:00' then
set user_session_id = 2;  

else set user_session_id =3;

end case;  
end //

Canvas

thanks. tried above but still getting 1064 error ...'for the right syntax to use near '; case when time(request_time)... – Jebel

I've had another crack at it, try that. – Canvas

Thanks, tried it. again same but i figured it out. see edited answer. – Jebel

Answer 2

the case must match at least one of the given statements, otherwise you will get this error. To avoid the error include an else in it.

create procedure upd_userinput(in  request_time timestamp, out user_session_id int) 
begin 
update user_input;
case request_time
when time(request_time) < '9:15:00' && time(request_time) > '8:15:00' then
set user_session_id = 1; 
when  time(request_time)< '10:15:00' && time(request_time) > '11:15:00' then
set user_session_id =2; 
else set user_session_id =0; 
end case; 
end

Kaylyn

Answer 3

The best way To use a case-when statement in a mysql stored procedure is:

DELIMITER $
CREATE PROCEDURE my_pro()
BEGIN
UPDATE users 
SET phone = 
(
    CASE 
        WHEN substr(phone, 2,1) = '3' THEN concat('04', substr(phone, 2))
        WHEN substr(phone, 2,1) = '2' THEN concat('05', substr(phone, 2))
    END
);
END $

Batholomew