MySQL Forums
Forum List  »  Stored Procedures

Re: Stored Proceedure Not doing anything...?
Posted by: Joe Charles
Date: June 02, 2018 12:33PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Stored Proceedure Not doing anything...?
173
June 02, 2018 12:33PM


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.