How to use case-when statements in stored mysql procedure?

[Charles Ndethi]

Hi, 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.

[Rabbit]

Try getting rid of request_time in the line case request_time.

[Charles Ndethi]

I have but still i get error 1064..
I cant see what's wrong with the procedure

[Rabbit]

Well, if you're trying to run an update SQL statement, the syntax is incorrect. You'll want to look it up.

[Charles Ndethi]

Thanks, I tried that plus I changed the order of assigning time to time(request_ti me) and i added a new parameter..it worked.see below:

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 //