There are several problems with your code:
- You have to use
CREATE PROCEDURE instead of just CREATE;
- Your
UPDATE syntax was wrong. SET goes before WHERE clause.
- 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