MySQL Error in query statements

Question

I am trying to acquaint myself on Mysql syntax. I only have used MSSQL so far. I downloaded the MySQL Query Browser and have installed the MySQL Version 5.1

I wanted to run this line of code in the resultset tab of MySQL but I keep on encountering below error

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 'declare iCtr int' at line 1

Code:

declare iCtr int;
set iCtr = 1;

while iCtr < 1000
begin
    insert into employee (emp_id,emp_first_name,emp_last_name,status_id)
      values (iCtr, 'firstName' + iCtr, 'lastName' + iCtr, 1)
      set iCtr = iCtr + 1;
end

I just wanted to populate my employees table but I cannot get past the MySQL syntax.

Nanne · Accepted Answer · 2011-01-06 08:49:14Z

1

from: http://dev.mysql.com/doc/refman/5.0/en/declare.html

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

I think that might be the trouble?

answered Jan 6, 2011 at 8:49

Nanne

64.4k16 gold badges122 silver badges166 bronze badges

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

3 Comments

Mark Estrada Over a year ago

Thanks for your reply. I tried what you have suggested but I still encounters the same error. Any other hints please.

Nanne Over a year ago

what did you try, and what is the new error? Because the linenumber must have changed at least ;)

Mark Estrada Over a year ago

I tried what you have suggested the error is like this: 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 'declare iCtr int' at line 2 =)

Nik · Accepted Answer · 2011-01-06 08:55:12Z

1

try

DELIMITER $$


DROP PROCEDURE IF EXISTS `procedurename`$$

CREATE PROCEDURE `procedurename`()
BEGIN
        declare iCtr int;
set iCtr = 1;

while iCtr < 1000 do

    insert into employee (emp_id,emp_first_name,emp_last_name,status_id)
      values (iCtr, 'firstName' + iCtr, 'lastName' + iCtr, 1);
      set iCtr = iCtr + 1;
end while;

    END$$

DELIMITER ;

answered Jan 6, 2011 at 8:55

Nik

4,0653 gold badges23 silver badges16 bronze badges

2 Comments

Mark Estrada Over a year ago

Can I execute SQL statements on the fly without making them a procedure in Mysql? Thanks

Nik Over a year ago

@Mark: As per my knowledge, you can do it in two ways, first, making dynamic query using language like PHP and then you can execute it using mysql functions which are available with PHP. Second, If you want to do with mysql itself, then you will need to use procedure as i suggested . Hope this helps.

Collectives™ on Stack Overflow

MySQL Error in query statements

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related