Syntax errors creating a stored procedure

Question

Create procedure Movestaff (input_staffID smallINT, new_deptID tinyINT)
BEGIN
 declare old_deptID tinyINT;
 start transaction;
 select departmentID into old_deptID from staff where staffID=input_staffID;
 update staff set departmentID = new_deptID where staffID=input_staffID;
 update department set staffCount = staffCount-1 where departmentID =old_deptID;
 update department set staffCount = staffCount+1 where departmentID =new_deptID;
 Commit;
END

My teacher at uni provided us with this stored procedure to analyse and evaluate, except it doesn't work when trying to execute it. It's too late now to receive a reply (5pm). We only started this content recently. I'm receiving a generic MySQL error on line 3 - declare old_deptID.

1064 - 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 '' at line 3

Line 3 - so right where the delimiter occurs

Strawberry
– Strawberry

2017-05-02 07:08:35 +00:00
Commented May 2, 2017 at 7:08 — Strawberry
– Strawberry, Commented May 2, 2017 at 7:08

Nigel Ren · Accepted Answer · 2017-05-02 06:51:10Z

2

A common problem with stored procedures is that the delimiter normally used by the language ( a ';') to indicate the end of a statement. Of course with stored procedures, there may be several statements inside one large block of code - as in your example. The way round this is to change the delimiter:

delimiter $$

So now it will execute everything until the next $$ as one piece of code. So do this prior to running your statement. Then run your code with a $$ at the end. Don't forget to change it back after with

delimiter ;

answered May 2, 2017 at 6:51

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

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

Collectives™ on Stack Overflow

Syntax errors creating a stored procedure

1064 - 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 '' at line 3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1064 - 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 '' at line 3

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related