CODEWITHSUNDEEP
mysqlstored-proceduresdelimiter
Carsten Flokstra
Carsten Flokstra

Reputation: 65

MySQL Error by creating Stored Procedure

I want to make a Stored Procedure that handles an order from our site. The problem is that when I run the script there is a MySQL syntax error. I'm very new with MySQL Stored Procedures. Can someone look at my code?

USE postbrood;
DELIMITER //
CREATE PROCEDURE MaakBestelling(IN KlantIDParam INT, IN ProductIDArray VARCHAR(255), IN AantalArray VARCHAR(255))

BEGIN
DECLARE BestelID INT DEFAULT 0;
DECLARE ArrayLenght INT DEFAULT 0;
DECLARE Counter INT DEFAULT 0;



SET ArrayLenght = LENGTH(ProductIDArray) - LENGTH(REPLACE(ProductIDArray, ',', '')) + 1;


INSERT INTO bestelling(klantID) VALUES (KlantIDParam);
SET BestelID = LAST_INSERT_ID();

WHILE Counter < ArrayLenght DO
INSERT INTO bestelregel VALUES (SUBSTRING_INDEX(ProductIDArray,',',Counter),BestelID,SUBSTRING_INDEX(AantalArray,',',Counter));
SET Counter = Counter + 1;
END WHILE;
END//
DELIMITER ;

Thanks in advance!

Upvotes: 1

Views: 51

Answers (2)

Carsten Flokstra
Carsten Flokstra

Reputation: 65

Nailed it! :)

USE postbrood;
DELIMITER //
CREATE PROCEDURE MaakBestelling(IN KlantIDParam INT, IN ProductIDArray VARCHAR(255), IN AantalArray VARCHAR(255))

BEGIN
DECLARE BestelID INT DEFAULT 0;
DECLARE ArrayLenght INT DEFAULT 0;
DECLARE Counter INT DEFAULT 0;



SET ArrayLenght = LENGTH(ProductIDArray) - LENGTH(REPLACE(ProductIDArray, ',', '')) + 1;


INSERT INTO bestelling(klantID) VALUES (KlantIDParam);
SET BestelID = LAST_INSERT_ID();

WHILE Counter < ArrayLenght DO
INSERT INTO bestelregel VALUES (SUBSTRING_INDEX(ProductIDArray,',',Counter),BestelID,SUBSTRING_INDEX(AantalArray,',',Counter),2.50);
SET Counter = Counter + 1;
END WHILE;
END//
DELIMITER ;

Upvotes: 1

Wim
Wim

Reputation: 85

I think the comma is not being recognized at

SET ArrayLenght = LENGTH(ProductIDArray) - LENGTH(REPLACE(ProductIDArray, ',', '')) + 1;

You should try replacing the comma with the ascii entity number: &#44;

Upvotes: 0

Related Questions