mysql multiple stored procedure update

Question

I'm having some trouble with mysql updateusers stored procedure that look like this:

 DELIMITER go

 Create procedure updateusers(
   IN UserID tinyint(11),
   IN FirstName varchar(30),
   IN LastName varchar(30),
   IN Password varchar(30),
   IN EmailAddress varchar(30),
   IN Salt varchar(40),
   IN RoleID varchar(1))
 BEGIN
   update  users
   set
     FirstName = FirstName
     where UserID = UserID
 End
 BEGIN
   update  users
   set
     LastName = LastName
     where UserID = UserID

 End
 BEGIN
   update  users
   set     
     Password = Password
     where UserID = UserID

 End
 BEGIN
   update  users
   set
     EmailAddress = EmailAddress
     where UserID = UserID
 End
 BEGIN
   update  users
   set
     Salt = Salt
     where UserID = UserID
 End

 BEGIN
   update  users
   set
     RoleID = RoleID
     where UserID = UserID;  
 End
 go

 DELIMITER ;

and I got an error in line 16 that says:

MySQL said: Documentation #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
'End BEGIN update  users set
LastName = LastName
where UserID = Us' at line 16

Then I would have used the call stored procedure like this:

call updateusers(3,'John','Jamieson','dsd','jamie@gmail.com','abac123','U')

The output that I would like to get is for example if I want to update from:

 UserId  FirstName  LastName  Password  EmailAddress    Salt  RoleID
 3       John       Smith     abc       john@gmail.com  123   U

to this:

 UserId  FirstName  LastName  Password  EmailAddress       Salt    RoleID
 3       John       Jamieson    dsd       jamie@gmail.com  abac123   U

or

UserId  FirstName  LastName  Password  EmailAddress       Salt    RoleID
 3       Aaron     Smith     abc       john@gmail.com     123     A

score 0 · Answer 1 · edited May 23 '17 at 10:28

Admittedly, the documentation for MySQL stored procedures has never been very good. There are not enough examples.

In your case, I would write the procedure like this:

 DELIMITER go

 CREATE PROCEDURE updateusers(
   IN inUserID tinyint,
   IN inFirstName varchar(30),
   IN inLastName varchar(30),
   IN inPassword varchar(30),
   IN inEmailAddress varchar(30),
   IN inSalt varchar(40),
   IN inRoleID varchar(1))
 BEGIN
   UPDATE users
   SET
     FirstName = inFirstName,
     LastName = inLastName,
     Password = inPassword,
     EmailAddress = inEmailAddress,
     Salt = inSalt,
     RoleID = inRoleID
   WHERE UserID = inUserID;
 END
 go

 DELIMITER ;

Changes:

Name your input parameters something distinct from the column names in your table, otherwise it's ambiguous if you want to set FirstName to the input parameter of the same name, or to itself (which would be a no-op). To remove the ambiguity, I just used "in" as a prefix for the input parameters.
You can update more than one column in a single UPDATE. In fact, you should do this, so you only need to run one query to find the row to be updated.
You don't need a BEGIN...END around every statement, just around blocks of statements, similar to how you use curly-braces in many programming languages.
Terminate your UPDATE statement with a semicolon.
I changed tinyint(11) to simply tinyint. The length argument doesn't do anything. Read my answer to Types in MySQL: BigInt(20) vs Int(20)

Thank you that solves the problem and you are right the mysql stored procedures for documentation examples are not good compared to mssql. — David Jones, Aug 23 '16 at 17:42
In general, I find MySQL stored procedures to be not worth the trouble. They are hard to develop, and the documentation is poor. They don't support packages or debugging. They don't compile, and their performance is poor. Most developers in the MySQL user community don't use procedures, they just write SQL statements in their application code. — Bill Karwin, Aug 23 '16 at 18:03

mysql multiple stored procedure update

1 Answers1