#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    1
    Rep Power
    0

    Store Procedure for Insert and Update


    hi everybody hope you can help me with the following problem on stored procedures :

    I need the data update process of the table if the record exists, otherwise insert the record:

    DELIMITER //

    CREATE PROCEDURE saveorUpdate(in producto varchar(30), precio int, existencias int, activo varchar(5))

    BEGIN

    DECLARE id int;

    SELECT id_pro FROM productos WHERE producto=producto into id;

    IF(id_pro=id)THEN

    UPDATE productos SET producto=producto, precio=precio, existencias=existencias, activo=activo
    WHERE id_pro=id;

    ELSE

    INSERT INTO productos (producto, precio, existencias, activo) VALUES
    (producto, precio, existencias, activo);

    END IF;
    END

    thanks for the help
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,953
    Rep Power
    4033
    Assuming your DB is MySQL, you don't need a procedure for this. Just use the INSERT INTO ... ON DUPLICATE KEY syntax.
    Code:
    INSERT INTO productos (
      producto , precio , existencias , activo 
    ) VALUES (
      $producto, $precio, $existencias, $activo
    ) ON DUPLICATE KEY UPDATE
      producto=VALUES(producto)
     , precio=VALUES(precio)
     , existencias=VALUES(existencias)
     , activo=VALUES(activo)
    You just need to make sure you have a UNIQUE constraint set on your producto column.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud

IMN logo majestic logo threadwatch logo seochat tools logo