Re: Stored Proceedure Not doing anything...?

[Joe Charles]

Thank you for the reply,

I have had it rewritten, is this better programming?



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

DROP procedure IF EXISTS `duplicate_product_records1`;
DELIMITER $$
USE `cliplabe1_db`$$
CREATE PROCEDURE `duplicate_product_records1`(IN `subcategory_id` INT, IN `new_subcategory_id` INT)
BEGIN
DECLARE _new_product_id INT;
DECLARE _new_product_id2 INT;
DECLARE _loop_eof BOOLEAN DEFAULT FALSE;
DECLARE _prod_id INT;
DECLARE _new_category_id INT;
DECLARE cur0 CURSOR FOR SELECT ProductID FROM products WHERE SubCatagoryID = subcategory_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _loop_eof = TRUE;
-- 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;
set _new_product_id2 = (SELECT MAX(`ProductID`) FROM `products`)+1;
-- INSERT new products from subcategory id selected
INSERT INTO `products` (
`productid`,`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_product_id2 ,_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`);

-- 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 ;