Hello all I have problem with a procedure. I want to use an if statement with stored procedure Here is procedure: CREATE DEFINER = 'root'@'localhost' PROCEDURE `mytmp`() NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN select * from product where product.product_status = 0; END; Code (markup): Now what i want to do is, place an if statement which will check if the expiry date of product is greater then the current date. if the date is greater then i will change the status of that product. any help thank you
I don't think you need an IF statement. CREATE DEFINER = 'root'@'localhost' PROCEDURE `mytmp`() NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN UPDATE product SET product_status = 1 WHERE product_status = 0 AND expiry_date > CURDATE(); END; Code (markup):
Agree with the above, this isnt a case for an IF statement but simply an SQL statement with the where clause filtering to the relevant sections.
thank you all for replying and trying to help me. there is an issue: all the expiry dates are some other table for this I think i need to use if statement, because I had to calculate it first and then apply it on data. tables: Products Contains productid, status,expid Expiry expid, days thank you
No, just join the tables in the query like: UPDATE Expiry INNER JOIN Products ON Expiry.ExpID = Products.ExpID SET Products.Product_Status = 1 WHERE Products.Product_Status = 0 AND Expiry.Date < CURDATE(); Code (SQL):