Re: Stored Proceedure Not doing anything...?
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 CONTINUE HANDLER FOR NOT FOUND SET _loop_eof = TRUE;
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`,
`Keywords`, `BigPict`, `ProductPict`, `DateAdded`, `Special`, `Quantity`, `tradeprice`, `FilePath`
)
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 ;