From: amit Date: December 3 2012 1:40pm Subject: MUltiple value in single insert is not working in mysql procedure List-Archive: http://lists.mysql.com/mysql/228752 Message-Id: <50BCABE6.7080601@netcore.co.in> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------010008010805030402010406" --------------010008010805030402010406 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi Team, I am not able to use multi value insert via argument in mysql stored procedure, Please help where am I wrong. Thanks in Advance ! mysql> insert into input_data1 values(7777),(6666),(5555),(9999); Query OK, 4 rows affected (0.00 sec) *Problem* mysql> call mobile_series1('(99889988),(12334565)'); +-----------------------+ | mobile | +-----------------------+ | (99889988),(12334565) | +-----------------------+ 1 row in set (0.01 sec) instead of 2 rows in table its only one row. *Function Code:* |DELIMITER| DROP PROCEDURE IF EXISTS mobile_series1; CREATE PROCEDURE mobile_series1(IN str text) LANGUAGE SQL READS SQL DATA BEGIN DROP TABLE IF EXISTS input_data1; CREATE TEMPORARYTABLE input_data1(mobile varchar(1000)) engine=memory; INSERT INTO input_data1(mobile) VALUES (str); SELECT * FROM input_data1; END | DELIMITER; | -- Best Regards Amit Jain www.netcore.co.in Ext - 4109 #9833777592 ** Message has been scanned by Emergic MailServ ** --------------010008010805030402010406 Content-type: multipart/related; boundary="----------=_1354542061-29809-55" This is a multi-part message in MIME format... ------------=_1354542061-29809-55 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi Team,

I am not able to use multi value insert via argument in mysql stored procedure, Please help where am I wrong. Thanks in Advance !

mysql> insert into input_data1 values(7777),(6666),(5555),(9999);
Query OK, 4 rows affected (0.00 sec)

Problem
mysql> call mobile_series1('(99889988),(12334565)');
+-----------------------+
| mobile                |
+-----------------------+
| (99889988),(12334565) |
+-----------------------+
1 row in set (0.01 sec)

instead of 2 rows in table its only one row.

Function Code:
DELIMITER |
DROP PROCEDURE IF EXISTS mobile_series1;
CREATE PROCEDURE mobile_series1(IN str text)  
    LANGUAGE SQL     READS SQL DATA  
    BEGIN    
         DROP TABLE IF EXISTS input_data1 ;  
         CREATE TEMPORARY TABLE input_data1 (mobile varchar(1000)) engine=memory;
         INSERT INTO input_data1(mobile) VALUES (str);
	 SELECT * FROM input_data1;
 END |
DELIMITER ;





-- 
Best Regards 
Amit Jain
www.netcore.co.in
Ext - 4109
#9833777592




Banner plane









                     
netCORE wins GOLD in 1st Smarties India 2012 Awards                                                

                                                                                                                                                                                        

                                                                                                                                                                                         
Organized by Mobile Marketing Association (MMA)                                                                                                                                                                               





Cross Media Integration for 'Colgate Active Salt Taste Challenge 2012' Campaign




Campaigns nominated for other categories 














Brand Awareness

Active Wheel Mobile Marketing Campaign


Innovation

Active Wheel Mobile Marketing Campaign
------------=_1354542061-29809-55 Content-Type: image/jpeg; name="signature.jpg" Content-Disposition: inline; filename="signature.jpg" Content-Transfer-Encoding: base64 MIME-Version: 1.0 X-Mailer: MIME-tools 5.420 (Entity 5.420) Content-ID: CjxtZXRhIGh0dHAtZXF1aXY9IkNvbnRleHQtVHlwZSIgY29udGVudD0idGV4 dC9odG1sOyBjaGFyc2V0PUlTTy04ODU5LTEiPgo8aHRtbD4NCjxoZWFkPg0K PHRpdGxlPnJlZmZlcmFsIGJhbm5lcjwvdGl0bGU+DQo8bWV0YSBodHRwLWVx dWl2PSJDb250ZW50LVR5cGUiIGNvbnRlbnQ9InRleHQvaHRtbDsgY2hhcnNl dD1pc28tODg1OS0xIj4NCjwvaGVhZD4NCjxib2R5IGJnY29sb3I9IiNGRkZG RkYiIGxlZnRtYXJnaW49IjAiIHRvcG1hcmdpbj0iMCIgbWFyZ2lud2lkdGg9 IjAiIG1hcmdpbmhlaWdodD0iMCI+DQoNCjx0YWJsZSBpZD0iVGFibGVfMDEi IHdpZHRoPSI2MDAiIGhlaWdodD0iMTI1IiBib3JkZXI9IjAiIGNlbGxwYWRk aW5nPSIwIiBjZWxsc3BhY2luZz0iMCI+DQoJPHRyPg0KCQk8dGQ+DQoJCQk8 YSBocmVmPSJodHRwOi8vd3d3Lm5ldGNvcmUuY28uaW4vZW1haWwtbWFya2V0 aW5nL3JlZmVyLyIgdGFyZ2V0PSJfYmxhbmsiPjxpbWcgc3JjPSJodHRwOi8v d3d3Lm5ldGNvcmUuY28uaW4vZW1haWwtbWFya2V0aW5nL3JlZmVyL2Jhbm5l cl9odG1sL2ltYWdlcy9yZWZmZXJhbC1iYW5uZXJfMDEuanBnIiBhbHQ9IiIg d2lkdGg9IjE1MCIgaGVpZ2h0PSIxMjUiIGJvcmRlcj0iMCIgc3R5bGU9ImRp c3BsYXk6YmxvY2s7Ij48L2E+PC90ZD4NCgkJPHRkPg0KCQkJPGEgaHJlZj0i aHR0cDovL3d3dy5uZXRjb3JlLmNvLmluL2VtYWlsLW1hcmtldGluZy9yZWZl ci8iIHRhcmdldD0iX2JsYW5rIj48aW1nIHNyYz0iaHR0cDovL3d3dy5uZXRj b3JlLmNvLmluL2VtYWlsLW1hcmtldGluZy9yZWZlci9iYW5uZXJfaHRtbC9p bWFnZXMvcmVmZmVyYWwtYmFubmVyXzAyLmpwZyIgYWx0PSIiIHdpZHRoPSIx NTAiIGhlaWdodD0iMTI1IiBib3JkZXI9IjAiIHN0eWxlPSJkaXNwbGF5OmJs b2NrOyI+PC9hPjwvdGQ+DQoJCTx0ZD4NCgkJCTxhIGhyZWY9Imh0dHA6Ly93 d3cubmV0Y29yZS5jby5pbi9lbWFpbC1tYXJrZXRpbmcvcmVmZXIvIiB0YXJn ZXQ9Il9ibGFuayI+PGltZyBzcmM9Imh0dHA6Ly93d3cubmV0Y29yZS5jby5p bi9lbWFpbC1tYXJrZXRpbmcvcmVmZXIvYmFubmVyX2h0bWwvaW1hZ2VzL3Jl ZmZlcmFsLWJhbm5lcl8wMy5qcGciIGFsdD0iIiB3aWR0aD0iMTUwIiBoZWln aHQ9IjEyNSIgYm9yZGVyPSIwIiBzdHlsZT0iZGlzcGxheTpibG9jazsiPjwv YT48L3RkPg0KCQk8dGQ+DQoJCQkJPGEgaHJlZj0iaHR0cDovL3d3dy5uZXRj b3JlLmNvLmluL2VtYWlsLW1hcmtldGluZy9yZWZlci8iPjxpbWcgc3JjPSJo dHRwOi8vd3d3Lm5ldGNvcmUuY28uaW4vZW1haWwtbWFya2V0aW5nL3JlZmVy L2Jhbm5lcl9odG1sL2ltYWdlcy9yZWZmZXJhbC1iYW5uZXJfMDQuanBnIiBh bHQ9IiIgd2lkdGg9IjE1MCIgaGVpZ2h0PSIxMjUiIGJvcmRlcj0iMCIgc3R5 bGU9ImRpc3BsYXk6YmxvY2s7Ij48L2E+PC90ZD4NCgk8L3RyPg0KPC90YWJs ZT4NCg0KPC9ib2R5Pg0KPC9odG1sPjxodG1sPjxib2R5PjwvYm9keT48L2h0 bWw+ ------------=_1354542061-29809-55-- --------------010008010805030402010406--