CODEWITHSUNDEEP
mysqlsqldatabasestored-proceduresif-statement
Mattews Bueno
Mattews Bueno

Reputation: 13

mysql if Syntax ERROR

I'm trying to create a procedure on mysql, it's not too hard, but when using "if" the things goes crazy, it says there's a error but i cant understand. Here it goes:

CREATE SP_Verify_DATE (IN comp INT,IN prod INT,IN qt INT)
BEGIN
SELECT COUNT(cd_compra) INTO @tes FROM produto_compra WHERE cd_produto=prod AND cd_compra=comp;

IF (@tes = 1) THEN

UPDATE produto_compra WHERE cd_produto=prod AND cd_compra=comp SET qt_produto = qt_produto + qt;
ELSE 

INSERT INTO produto_compra VALUES(comp,prod,qt);

END IF;

END

Many thanks to peterm !!! works !!!! what i lost 2 night's trying to find the error, and your ideia is better than my procedure i used it, thanks again !

Upvotes: 1

Views: 204

Answers (1)

peterm
peterm

Reputation: 92795

There are several problems with your code:

  1. You have to use CREATE PROCEDURE instead of just CREATE;
  2. Your UPDATE syntax was wrong. SET goes before WHERE clause.
  3. You need change DELIMITER

Try

DELIMITER $$
CREATE PROCEDURE SP_Verify_DATE (IN comp INT, IN prod INT, IN qt INT)
BEGIN
  SELECT COUNT(cd_compra) 
    INTO @tes 
    FROM produto_compra 
   WHERE cd_produto=prod 
     AND cd_compra=comp;

  IF (@tes = 1) THEN
    UPDATE produto_compra
       SET qt_produto = qt_produto + qt
     WHERE cd_produto=prod AND cd_compra=comp;
  ELSE 
    INSERT INTO produto_compra VALUES(comp,prod,qt);
  END IF;
END$$
DELIMITER ;

Other than that your code is OK.

Here is SQLFiddle demo

Now you can boil down the whole thing to just one insert statement if you have or if you define a unique index on (cd_produto, cd_compra) (and following the logic of your code you should have such constraint) using INSERT INTO ... ON DUPLICATE KEY UPDATE syntax

CREATE PROCEDURE SP_Verify_DATE (IN comp INT, IN prod INT, IN qt INT)
  INSERT INTO produto_compra (cd_produto, cd_compra, qt_produto)
  VALUES(comp, prod, qt)
  ON DUPLICATE KEY UPDATE qt_produto = qt_produto + VALUES(qt_produto);

Since it's a one-statement SP now you don't even need to use BEGIN ... END block and change delimiter.

Obviously if you need to you can opt-out from a stored procedure completely and use such insert statement on its own.

Here is SQLFiddle demo

Upvotes: 2

Related Questions