---------- Forwarded message ---------- From: Reindl Harald <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 <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
> 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.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
signature.asc
Description: PGP signature
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
