Mysql Stored Procedure Issue

Question

When creating the following procedure I receive this error message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'd_query;
 set d_result = execute stmt;
 deallocate prepare stmt; ' at line 15

But after checking I can't see the error. Line 15 relates to 'if d_query is not null then'.

Is this the true error? Or is it not happy to accept an execute as an assignment to d_result?

Any help would be greatly appreciated.

delimiter |
create procedure runtests()
begin
  declare d_test_id char(20);
  declare d_query text;
  declare d_result text;

  declare cur cursor for select test_id, query, result from datavalidator order by test_id;

  open cur;

  repeat
    fetch cur into d_test_id, d_query, d_result;

    if d_query is not null then
      prepare stmt from d_query;
      set d_result = execute stmt;
      deallocate prepare stmt;

      update datavalidator set result = d_result;
    end if;
  until done end repeat;

  close cur;
end;
|
delimiter ; |

Jeremy Smyth · Accepted Answer · 2009-08-24 10:44:06Z

1

"Note that a literal string expression or a user variable are the only ways you can specify the statement to be prepared.", from this documentation.

Try writing the value into a user variable before trying to prepare, perhaps something like:

set @q_sql = d_query;

...or write directly into the user variable in the fetch line.

answered Aug 24, 2009 at 10:44

Jeremy Smyth

23.6k2 gold badges57 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Stored Procedure Issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related