problem with stored procedure

Discussion in 'Databases' started by dizyn, Jan 20, 2008.

  1. #1
    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
     
    dizyn, Jan 20, 2008 IP
  2. SoKickIt

    SoKickIt Active Member

    Messages:
    305
    Likes Received:
    30
    Best Answers:
    0
    Trophy Points:
    70
    #2
    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):
     
    SoKickIt, Jan 20, 2008 IP
  3. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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.
     
    AstarothSolutions, Jan 20, 2008 IP
  4. dizyn

    dizyn Active Member

    Messages:
    251
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #4
    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
     
    dizyn, Jan 22, 2008 IP
  5. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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):
     
    AstarothSolutions, Jan 22, 2008 IP