List:General Discussion« Previous MessageNext Message »
From:Cifer Lee Date:March 26 2012 1:38pm
Subject:Fwd: why must user variable in EXECUTE USING clause ?
View as plain text  
---------- Forwarded message ----------
From: Reindl Harald <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 <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
> 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 <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

Attachment: [text/html]
Attachment: [application/pgp-signature] signature.asc
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