Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am converting a MSSQL Stored Procedure to MySQL, and am running into a problem. Maybe it's because I have been staring at the code for so long, but I can't seem to figure it out:

CREATE PROCEDURE usp_AddNewUser 
(IN UserName VARCHAR(50),
IN UserID VARCHAR(50),
IN Password VARCHAR(30),
IN PageAccess VARCHAR(10),
IN AdminUser VARCHAR(50))


BEGIN
Declare RowID, CountAll INT DEFAULT 0

SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID);


IF @CountAll = 0 THEN
    INSERT INTO WebUsers (UserName, UserID, UserPassword, AddedBy, UpdatedBy) Values (UserName, UserID, Password, AdminUser, AdminUser);
    SELECT @RowID := @@Identity;
ELSE
    SELECT @RowID := RowID FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID);
    UPDATE WebUsers SET UpdatedBy = @AdminUser, UpdatedOn = NOW() WHERE RowID = @RowID;
END IF;

If  @PageAccess Like ('%4%') THEN
    INSERT INTO WebUserTypeMapping (WebUserRowID, WebUserTypeRowID) Values (@RowID, 4);
ELSE

    If  @PageAccess Like ('%1%') THEN
        INSERT INTO WebUserTypeMapping (WebUserRowID, WebUserTypeRowID) Values (@RowID, 1);
    END IF;
END IF;
END;

The syntax error is at the SELECT@CountAll line:

SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID) 

I've tried rewriting it several times, but no dice. There are likely additional errors in this code too that I don't know about yet since I can't get by that error.

Can anyone help me out? Not that it matters, but it would totally make my Friday... :)

Thanks!

Edit: The full error message is the following:

#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 'SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID)' at line 12 

MySQL Server version: 5.5.30

Edit: When setting a different delimiter, I get an error message that literally just says "Error" -- with no other information.

share|improve this question
    
@@Identity? GETDATE()? GO? Have you checked if MySQL has these or what the equivalents are? –  ypercube Jun 20 at 18:36
    
Also read this about system, user and local in stored procedures (declared) variables: SET syntax and Variables in stored programs –  ypercube Jun 20 at 18:42
    
Thanks for the suggestions! I removed @@Identity (I don't think it's necessary), put NOW() instead of GETDATE(), and removed GO, but that pesky error keeps popping up about the same line :) Will definitely continue my research though into the syntax's. –  Mike Jun 20 at 18:48
1  
Read the documentation (there are also several tutorials out there.) CountAll and @CountAll are not the same thing by the way. And use semicolons (;) when a stetement ends. MySQL is not so forgiving as SQL-Server at this use. –  ypercube Jun 20 at 18:51
    
Yup, added semicolons already on my end. The original MSSQL code (not written by me) used the whole RTRIM(UserID) = RTRIM(@UserID) ---- so I believe the @ is intentional for whatever reason. Thanks for all your suggestions! –  Mike Jun 20 at 18:54

2 Answers 2

Comment from @ypercube is correct -- in this case, the syntax error you're encountering is because the previous line did not end in a semicolon:

Declare RowID, CountAll INT DEFAULT 0

Should be:

Declare RowID, CountAll INT DEFAULT 0;

In MySQL's stored procedure language, you have access to two different types of variables.

  • The first you declare with DECLARE and these are locally scoped to the procedure. Do not prefix these with @.

  • The second type of variable are called user variables and they are prefixed with @. You don't have to declare these. But they are kind of like "global" variables in that they are scoped to the session, and you can assign and read them outside the procedure. That is, you could assign a value to one of these variables in the procedure, and then after the procedure is done, you could read that user variable in any SQL expression.

In your procedure, you also have some input arguments. These are treated like the first type of variable. They are locally scoped, and they don't use the @ prefix. For example, you have an input argument UserID but this is not the same variable as @UserID that you use in your query.

This brings up the possibility of ambiguity when your variables have the same names as columns, and you try to use the variables to compare to the columns. MySQL does its best to have rules for precedence, but it is far easier to debug if you make sure your local variable names don't use the same names as columns.


Other issues:

  • This line:

    SELECT @RowID := @@Identity;
    

    Should be as follows in MySQL:

    SET @RowID = LAST_INSERT_ID();
    
  • Comments starting with -- must have a space following them, which is non-ANSI but is meant to resolve syntax ambiguity. E.g., this:

    --User
    

    Must be changed to:

    -- User
    
share|improve this answer
    
Thank you for the response! Unfortunately, after adding a semicolon at the end of the declare, that line starts to flag a syntax error like so: "#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 10". Thank you for the LAST_INSERT_ID :) –  Mike Jun 20 at 19:34

In MySQL the assignment in a SELECT is done with :=

SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID) 
share|improve this answer
    
Thanks! Still not working though :) –  Mike Jun 20 at 18:08
    
All the assignation in a SELECT, not only the first one –  Serpiton Jun 20 at 18:09
    
Done! But still the same syntax error. I appreciate you spotting that though. –  Mike Jun 20 at 18:12
    
The error is no more in that line, the only other think I see is @@Error, MySQL doesn't have that. Also stop editing the question removing the errors pointed out, It doesn't help anyone, not even yourself. –  Serpiton Jun 20 at 18:31
    
I figured it would be more useful to have the updated code on there for future suggestions, but ok. Also, the error persists without the @@Error –  Mike Jun 20 at 18:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.