CODEWITHSUNDEEP
mysql
Josh
Josh

Reputation: 3100

Syntax errors creating a stored procedure

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

Upvotes: 1

Views: 31

Answers (1)

Nigel Ren
Nigel Ren

Reputation: 57121

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 ;

Upvotes: 2

Related Questions