Create Stored Procedure Insert and Update

April 9, 2022

I’m trying to insert and update queries at the same time in store procedure,
but taking syntax error

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘@product_id int(11), IN @ProductCode varchar(255), IN @ProductName varchar (2…’ at line 1

my query is here

DELIMITER //
CREATE PROCEDURE UpdateProductList(IN @product_id int(11), IN @ProductCode varchar(255), IN @ProductName varchar (255), IN @PiecesInBox varchar (255), IN @Price varchar (255))
BEGIN
IF @product_id = 0
INSERT INTO products(ProductCode, ProductName, PiecesInBox, Price) VALUES(@ProductCode, @ProductName, @PiecesInBox, @Price);
ELSE
UPDATE products SET ProductCode = @ProductCode, ProductName = @ProductName, PiecesInBox = @PiecesInBox, Price = @Price WHERE product_id = @product_id;
END IF
END //
DELIMITER ;

>Solution :

Avoid using @ as they’re reserved for global variables in MariaDB:

  DELIMITER //
  
  CREATE PROCEDURE UpdateProductList(
     IN in_product_id     INT(11),
     IN in_ProductCode    VARCHAR(255), 
     IN in_ProductName    VARCHAR(255), 
     IN in_PiecesInBox    VARCHAR(255), 
     IN in_Price          VARCHAR(255)
  )
  BEGIN
     IF in_product_id = 0 THEN
        INSERT INTO 
            products(ProductCode, ProductName, PiecesInBox, Price) 
        VALUES
            (in_ProductCode, in_ProductName, in_PiecesInBox, in_Price);
     ELSE
        UPDATE 
            products 
        SET 
            ProductCode = in_ProductCode, 
            ProductName = in_ProductName, 
            PiecesInBox = in_PiecesInBox, 
            Price       = in_Price 
        WHERE 
            product_id = in_product_id;
     END IF;
  END //
  
  DELIMITER ;

If the query still doesn’t run, print the given error and I may update the answer.