---------- 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
> CREATE PROCEDURE `iter_table`(IN type int)
> 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;
> 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
> SET count=count+1;
> UNTIL count >= row_count
> END REPEAT;
> CLOSE cur1;
> 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..
> On Mon, Mar 26, 2012 at 8:15 PM, Reindl Harald <h.reindl@stripped<mailto:
> 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: [application/pgp-signature] signature.asc