Procedure doesn't work
Hi, I'm trying to make a procedure for a Prestashop 1.4 database and I don't see where is the error.
This is the error that MySQL sends to me:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE vRproduct int(10); DECLARE vRattribute int(10); DECLARE vRname v' at line 63
And this is the code of procedure:
------------------------------------------
delimiter //
CREATE PROCEDURE abono2 (OUT pedido INT(10), OUT entrega INT(10), OUT facturacion INT(10), OUT cliente INT(10))
BEGIN
-- DECLARACIÓN DE LAS VARIABLES
DECLARE vorder int(10);
DECLARE vcarrier int(10);
DECLARE vlang int(10);
DECLARE vcustomer int(10);
DECLARE vcart int(10);
DECLARE vcurrency int(10);
DECLARE vdelivery int(10);
DECLARE vinvoice int(10);
DECLARE vsecure varchar(32);
DECLARE vpayment varchar(255);
DECLARE vconversion decimal(13,6);
DECLARE vmodule varchar(255);
DECLARE vrecyclable tinyint(1);
DECLARE vgift tinyint(1);
DECLARE vgmess text;
DECLARE vshipping varchar(32);
DECLARE vdiscounts decimal(17,2);
DECLARE vpaid decimal(17,2);
DECLARE vpreal decimal(17,2);
DECLARE vproducts decimal(17,2);
DECLARE vproductswt decimal(17,2);
DECLARE vtotalship decimal(17,2);
DECLARE vcarrier2 decimal(10,3);
DECLARE vtotalwrap decimal(17,2);
DECLARE vinvoicenum int(10);
DECLARE vdeliverynum int(10);
DECLARE vinvoicedate datetime;
DECLARE vdeliverdate datetime;
DECLARE vvalid int(1);
DECLARE vdateadd datetime;
DECLARE vdateupd datetime;
-- DECLARACIÓN DE LOS CURSORES
DECLARE cur1 CURSOR FOR SELECT MAX(id_order),id_carrier,id_lang,id_customer,id_cart,id_currency,id_address_delivery,id_address_invoice,secure_key,payment,conversion_rate,module,recyclable,gift,gift_message,shipping_number,total_discounts,total_paid,total_paid_real,total_products,total_products_wt,total_shipping,carrier_tax_rate,total_wrapping,invoice_number,delivery_number,invoice_date,delivery_date,valid,date_add,date_upd FROM ps_orders WHERE id_customer = cliente AND id_address_delivery = entrega AND id_address_invoice = facturacion AND id_order = pedido;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO vorder,vcarrier,vlang,vcustomer,vcart,vcurrency,vdelivery,vinvoice,vsecure,vpayment,vconversion,vmodule,vrecyclable,vgift,vgmess,vshipping,vdiscounts,vpaid,vpreal,vproducts,vproductswt,vtotalship,vcarrier2,vtotalwrap,vinvoicenum,vdeliverynum,vinvoicedate,vdeliverdate,vvalid,vdateadd,vdateupd;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO ps_orders (id_order,id_carrier,id_lang,id_customer,id_cart,id_currency,id_address_delivery,id_address_invoice,secure_key,payment,conversion_rate,module,recyclable,gift,gift_message,shipping_number,total_discounts,total_paid,total_paid_real,total_products,total_products_wt,total_shipping,carrier_tax_rate,total_wrapping,invoice_number,delivery_number,invoice_date,delivery_date,valid,date_add,date_upd) VALUES (vorder+1,vcarrier,vlang,vcustomer,vcart,vcurrency,vdelivery,vinvoice,vsecure,vpayment,vconversion,vmodule,vrecyclable,vgift,vgmess,vshipping,vdiscounts,vpaid*(-1),vpreal*(-1),vproducts*(-1),vproductswt*(-1),vtotalship*(-1),vcarrier2,vtotalwrap,vinvoicenum,vdeliverynum,vinvoicedate,vdeliverdate,vvalid,vdateadd,vdateupd);
END LOOP;
CLOSE cur1;
-- VARIABLES PARA EL SEGUNDO CURSOR
DECLARE vRproduct int(10);
DECLARE vRattribute int(10);
DECLARE vRname varchar(255);
DECLARE vRquantity int(10);
DECLARE vRstock int(10);
DECLARE vRrefunded int(10);
DECLARE vRreturn int(10);
DECLARE vRreinjected int(10);
DECLARE vRprice decimal(20,6);
DECLARE vRpercent decimal(10,2);
DECLARE vRamount decimal(20,6);
DECLARE vRreduction decimal(10,2);
DECLARE vRdiscount decimal(20,6);
DECLARE vRean13 varchar(13);
DECLARE vRupc varchar(12);
DECLARE vRreference varchar(32);
DECLARE vRsuppref varchar(32);
DECLARE vRweight float;
DECLARE vRtaxname varchar(40);
DECLARE vRtaxrate decimal(10,3);
DECLARE vRecotax decimal(21,6);
DECLARE vRecorate decimal(5,3);
DECLARE vRdiscoquant tinyint(1);
DECLARE vRhash varchar(255);
DECLARE vRnb int(10);
DECLARE vRdeadline datetime;
-- DECLARACIÓN DEL SEGUNDO CURSOR
DECLARE cur2 CURSOR FOR SELECT product_id,product_attribute_id,product_name,product_quantity,product_quantity_in_stock,product_quantity_refunded,product_quantity_return,product_quantity_reinjected,product_price,reduction_percent,reduction_amount,group_reduction,product_quantity_discount,product_ean13,product_upc,product_reference,product_supplier_reference,product_weight,tax_name,tax_rate,ecotax,ecotax_tax_rate,discount_quantity_applied,download_hash,download_nb,download_deadline FROM ps_order_detail WHERE id_order = pedido;
OPEN cur2;
read_loop: LOOP
FETCH cur2 INTO vRproduct,vRattribute,vRname,vRquantity,vRstock,vRrefunded,vRreturn,vRreinjected,vRprice,vRpercent,vRamount,vRreduction,vRdiscount,vRean13,vRupc,vRreference,vRsuppref,vRweight,vRtaxname,vRtaxrate,vRecotax,vRecorate,vRdiscoquant,vRhash,vRnb,vRdeadline;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO ps_order_detail (id_order,product_id,product_attribute_id,product_name,product_quantity,product_quantity_in_stock,product_quantity_refunded,product_quantity_return,product_quantity_reinjected,product_price,reduction_percent,reduction_amount,group_reduction,product_quantity_discount,product_ean13,product_upc,product_reference,product_supplier_reference,product_weight,tax_name,tax_rate,ecotax,ecotax_tax_rate,discount_quantity_applied,download_hash,download_nb,download_deadline) VALUES (vorder+1,vRproduct,vRattribute,vRname,vRquantity,vRstock,vRrefunded,vRreturn,vRreinjected,vRprice*(-1),vRpercent,vRamount,vRreduction,vRdiscount*(-1),vRean13,vRupc,vRreference,vRsuppref,vRweight,vRtaxname,vRtaxrate,vRecotax,vRecorate,vRdiscoquant,vRhash,vRnb,vRdeadline);
END LOOP;
CLOSE cur2;
END
//
------------------------------------------