MySQL Forums
Forum List  »  Stored Procedures

Procedure doesn't work
Posted by: Eduardo Rayas
Date: June 11, 2013 09:21AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Procedure doesn't work
3150
June 11, 2013 09:21AM
1059
June 11, 2013 12:34PM
968
June 12, 2013 08:28AM
976
June 17, 2013 10:45PM
937
June 18, 2013 02:31AM


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.