it seems none of you hava answered my question..... On Tue, Mar 27, 2012 at 7:01 AM, Walter Tross <wal...@waltertross.com>wrote:
> Harald, > keep in mind that > a) this mailing list is badly set up: unless you do a "reply all", the > reply goes only to the poster (happened to me too) > b) Microsoft products have made inline (bottom) answering almost > impossible. I, like many others, am forced to used Outlook at the office, > and therefore I had to give up, and now I stick to top-posting :-( > ciao > W. > P.S. the only thing I have not given up yet is not using empty lines to > avoid the "feature" (?) of Outlook that glues lines together. This > "feature" can be disabled/reverted (although this is not widely known) > > At 21.38 26/03/2012 +0800, Cifer Lee wrote: > > > >---------- Forwarded message ---------- > >From: Reindl Harald <<mailto:h.rei...@thelounge.net> > h.rei...@thelounge.net> > >Date: Mon, Mar 26, 2012 at 9:17 PM > >Subject: Re: why must user variable in EXECUTE USING clause ? > >To: Cifer Lee <<mailto:mantia...@gmail.com>mantia...@gmail.com> > > > > > >would you PLEASE send to the list instead off-list > >and put your answer BELOW instead to-posting? > > > >is it really so difficult to use mailing-lists? > > > > > >Am 26.03.2012 14:34, schrieb Cifer Lee: > >> thanks for reply > >> and .sorry for my poor English ... > >> I wrote a procedure program which contains prepare clause , please see > below > >> > >> CREATE PROCEDURE `iter_table`(IN type int) > >> BEGIN > >> DECLARE tablename VARCHAR(24) DEFAULT ''; > >> DECLARE shop_id int DEFAULT 0; > >> DECLARE count int DEFAULT 0; > >> DECLARE row_count int DEFAULT 0; > >> DECLARE x varchar(24); > >> DECLARE cur1 CURSOR FOR SELECT `id` FROM shop; > >> > >> SELECT COUNT(*) INTO row_count FROM shop; > >> > >> OPEN cur1; > >> REPEAT > >> FETCH cur1 INTO shop_id; > >> SET @shop_id := shop_id; > >> SET @type := type; > >> SET tablename= CONCAT('shop',@shop_id); > >> SET @sqlstr = CONCAT('SELECT > `id`,`name`,`repertory`,`photo`,`type`,`price`,@shop_id AS shop_id FROM > >> ',tablename,' WHERE `type`=?;'); > >> PREPARE stat FROM @sqlstr; > >> EXECUTE stat USING type; ----- Must be EXECUTE stat USING > @type ; > >> SET count=count+1; > >> UNTIL count >= row_count > >> END REPEAT; > >> CLOSE cur1; > >> END > >> > >> I got an error at the red line when I creating this procedure > >> and the solution is replace the 'type' with '@type' > >> > >> I found here <http://dev.mysql.com/doc/refman/5.5/en/execute.html> > http://dev.mysql.com/doc/refman/5.5/en/execute.html > >> and knows that > >> "/you must supply a |USING| clause that lists user variables containing > the values to be bound to the parameters. > >> Parameter values can be supplied only by user variables/," > >> > >> but, I don't know why . why must parameter be user variables ? why > can't be local variables , as declared in > >> DECLARE clause.. > >> > >> thanks! > >> > >> On Mon, Mar 26, 2012 at 8:15 PM, Reindl Harald <<mailto: > h.rei...@thelounge.net>h.rei...@thelounge.net <mailto: > h.rei...@thelounge.net>> wrote: > >> > >> > >> > >> Am 26.03.2012 14:13, schrieb Cifer Lee: > >> > why can't be local variable which declared in DECLARE clause? > >> > >> what are you speaking about? > >> keep in mind that we can not read your thoughts > > > > > >Content-Type: application/pgp-signature; name="signature.asc" > >Content-Disposition: attachment; filename="signature.asc" > >X-Attachment-Id: 2caae85bfd1f082d_0.1 > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: http://lists.mysql.com/mysql >
