MySQL Forums

Re: Stored Proceedure Not doing anything...?
Posted by: Joe Charles
Date: June 02, 2018 07:24AM

Im going to post the Code from the stored procedure called "duplicate_product_records" here, if there is anything obvious that would prevent it from running, please could someone point it out and tell me what to change?

===================================

There are 2 tables in the database that I'm supposed to be duplicating and modifying data in. The first is a table called "Products" the stored procedure is supposed to look for all the records in the database that match the "Subcatagoryid" in the first field and duplicate them in to the same table.

It is supposed to change the "Subcatagoryid" to the new one provided in the second field of the stored procedure and create a unique "productid" for the new record.

It is also supposed to select all the records in the "ProductDetails" database that match the "Productid" field from the original selected records and duplicate those too in to the "ProductDetails" table and update the "Productid" for each record to the new versions to match the new records in the "Products" table.

As the stored procedure that I have installed isn't doing anything, I don't know if it just has a bit missing, or needs totally rewriting.

Please could someone who knows what they are doing take a look for me:

=========================< Code below >==============================

DROP procedure IF EXISTS `duplicate_product_records`;

DELIMITER \$\$
USE `cliplabe1_db`\$\$
CREATE PROCEDURE `duplicate_product_records`(IN subcategory_id INT, IN new_subcategory_id INT)
BEGIN
DECLARE _new_product_id INT;
DECLARE _loop_eof BOOLEAN DEFAULT FALSE;
DECLARE _prod_id INT;
DECLARE cur0 CURSOR FOR SELECT ProductID FROM products WHERE SubCatagoryID = subcategory_id;
DECLARE _new_category_id INT;

-- Get the category ID
SET _new_category_id = (SELECT `CatagoryID` FROM `subcatagories` WHERE `SubCatagoryID` = new_subcategory_id);

OPEN cur0;
loop0: LOOP FETCH cur0 INTO _prod_id;
IF _loop_eof THEN
LEAVE loop0;
END IF;

-- INSERT new products from subcategory id selected
INSERT INTO `products` (
`CatagoryID`, `PTitle`, `DiscountAllowed`, `PDescription`, `SEO`, `PKeywords`, `ListPrice`, `LLength`, `LHeight`, `TMargin`,
`LMargin`, `HGap`, `VGap`, `PerBox`, `AveryCode`, `PerSheet`, `ProductRequirements`, `ItemType`, `SKU`, `SubCatagoryID`,
`ProductName`, `Description`, `QuantityPerUnit`, `UnitPrice`, `SpecialPrice`, `UnitWeight`, `Featured`, `InStock`, `LineItemTax`,
)
SELECT _new_category_id, `PTitle`, `DiscountAllowed`, `PDescription`, `SEO`, `PKeywords`, `ListPrice`, `LLength`, `LHeight`, `TMargin`, `LMargin`,
`HGap`, `VGap`, `PerBox`, `AveryCode`, `PerSheet`, `ProductRequirements`, `ItemType`,
`SKU`, new_subcategory_id, `ProductName`, `Description`, `QuantityPerUnit`, `UnitPrice`, `SpecialPrice`,
`UnitWeight`, `Featured`, `InStock`, `LineItemTax`, `Keywords`, `BigPict`, `ProductPict`, `DateAdded`, `Special`, `Quantity`,
`tradeprice`, `FilePath` FROM `products` WHERE `ProductID` = _prod_id;

-- Get the new product id added for the assign to new product details
SET _new_product_id = (SELECT MAX(`ProductID`) FROM `products` WHERE `SubCatagoryID` = subcategory_id);

-- Insert new product details for the new product
INSERT INTO `productdetails` (
`ProductID`, `P_Size`, `P_Color`, `UnitPrice`, `Discontinued`, `OnSpecial`, `pict`, `tradeprice`,
`Bigpict`, `listprice`, `specialprice`, `Quantity`
)
SELECT _new_product_id, `P_Size`, `P_Color`, `UnitPrice`, `Discontinued`, `OnSpecial`, `pict`, `tradeprice`,
`Bigpict`, `listprice`, `specialprice`, `Quantity` FROM `productdetails` WHERE `ProductID` = _prod_id;
END LOOP;
CLOSE cur0;
END\$\$

DELIMITER ;

Subject
Views
Written By
Posted
313
June 02, 2018 07:04AM
Re: Stored Proceedure Not doing anything...?
173
June 02, 2018 07:24AM
161
June 02, 2018 11:27AM
174
June 02, 2018 12:33PM
163
June 02, 2018 02:44PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.