i have three table product,sizes and product_size
product
> id
> name
product_size
> product_id FK
> size_id FK
size
> id
> name
Know using store procedure i want to insert single product with muliple sizes eg.
product.id =1;
product.name = xyz;
{
product_size.product_id = 1
product_size.size_id = 1
product_size.product_id = 1
product_size.size_id = 2
product_size.product_id = 1
product_size.size_id = 3
}
So how to pass sizes parameter to store procedure in mysql
The following is a stored procedure that inserts one record into product_size each time you call the procedure. The procedure takes input parameters for product.id and size.id.
DELIMITER //
CREATE PROCEDURE INSERT_product_size(
IN productID int,
IN sizeID int
)
BEGIN
INSERT INTO
product_size
(product_id, size_id)
VALUES
(productID, sizeID);
END //
DELIMITER ;
This procedure takes a single product id and an "array" of size id's (in the form of a comma-delimited string) and inserts all sizes for the product in one procedure call:
DROP PROCEDURE IF EXISTS INSERT_product_sizes;
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS INSERT_product_sizes(
IN productID int,
IN sizeIDs varchar(100)
)
BEGIN
DECLARE delimiterCount int;
DECLARE sizeID int;
DECLARE loopCount int;
/* Remove spaces, if any, from input string */
SET sizeIDs = REPLACE(sizeIDs, ' ', '');
/* Determine how many commas are in input string */
SET delimiterCount = LENGTH(sizeIDs) - LENGTH(REPLACE(sizeIDs, ',', ''));
SET loopCount = 1;
/* For each id in input string */
WHILE loopCount <= delimiterCount + 1 DO
SET sizeID = SUBSTRING_INDEX(sizeIDs, ',', 1);
INSERT INTO
product_size
(product_id, size_id)
VALUES
(productID, sizeID);
/* Remove last used id from input string */
SET sizeIDs = REPLACE(sizeIDs, CONCAT(sizeID, ','), '');
SET loopCount = loopCount + 1;
END WHILE;
END //
DELIMITER ;
If you want all sizes attached to a product on an insert then a simple
insert into product_size(product_id,size_id)
select inprodid,name
from size
;
will do. But if you only want some sizes then you could pass the sizes as a delimited string , split them in the procedure in a loop which would include an insert statement. You will find examples of how split strings if you google mysql split string.
