CODEWITHSUNDEEP
mysql
Sagar Shinde
Sagar Shinde

Reputation: 95

how to insert multiple rows in table using store procedure parameter in mysql

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

Upvotes: 0

Views: 2366

Answers (2)

SeanW333
SeanW333

Reputation: 489

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 ;

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17615

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.

Upvotes: 0

Related Questions