List:General Discussion« Previous MessageNext Message »
From:Cifer Lee Date:March 27 2012 7:28am
Subject:Re: Fwd: why must user variable in EXECUTE USING clause ?
View as plain text  
it seems none of you hava answered my question.....

On Tue, Mar 27, 2012 at 7:01 AM, Walter Tross <walter@stripped>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.reindl@stripped>
> h.reindl@stripped>
> >Date: Mon, Mar 26, 2012 at 9:17 PM
> >Subject: Re: why must user variable in EXECUTE USING clause ?
> >To: Cifer Lee <<mailto:mantianyu@stripped>mantianyu@stripped>
> >
> >
> >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.reindl@stripped>h.reindl@stripped <mailto:
> h.reindl@stripped>> 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
>

Thread
Fwd: why must user variable in EXECUTE USING clause ?Cifer Lee26 Mar
  • Re: Fwd: why must user variable in EXECUTE USING clause ?Walter Tross26 Mar
Re: Fwd: why must user variable in EXECUTE USING clause ?Cifer Lee27 Mar
  • Re: why must user variable in EXECUTE USING clause ?Johan De Meersman27 Mar