MySQL Forums

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

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;
-- 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`,
)
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 ;

Subject
Views
Written By
Posted
380
June 02, 2018 07:04AM
218
June 02, 2018 07:24AM
201
June 02, 2018 11:27AM
Re: Stored Proceedure Not doing anything...?
219
June 02, 2018 12:33PM
204
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.