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 ;
CodePudding user response:
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.