Mysql – Converting MSSQL Stored Procedure to MySQL – Syntax Error

MySQLstored-procedurest-sql

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.

Best Answer

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
```

Related Solutions

MySQL Trigger – Correct Syntax for UPDATE and Logging Changes

I indented everything and quickly noticed that the last END must have $$ after it, not the ;

USE `gknet`;
DELIMITER $$
CREATE TRIGGER `after_update_user` AFTER UPDATE ON `users` FOR EACH ROW
BEGIN
    IF NEW.loggedin != OLD.loggedin OR NEW.sessionkey != OLD.sessionkey THEN
        IF NEW.loggedin = '1' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry,  timestamp)
            VALUES (NEW.ID,'LOGGED_IN',OLD.sessionkey,NEW.sessionkey,NOW());
        ELSEIF NEW.loggedin = '0' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'LOGGED_OUT',OLD.sessionkey,NEW.sessionkey,NOW());
        END IF;
    END IF;
    IF NEW.locked != OLD.locked OR NEW.unlocktime != OLD.unlocktime THEN
        IF NEW.locked = '1' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'LOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
        ELSEIF NEW.locked = '0' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'UNLOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
        END IF;
    END IF;
    IF NEW.ip_address != OLD.ip_address THEN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
        VALUES (NEW.ID,'IP_CHANGE',OLD.ip_address,NEW.ip_address,NOW());
    END IF;
    IF NEW.verified != OLD.verified THEN
        IF NEW.verified = '1' THEN
            INSERT INTO user_records (userid, action, timestamp)
            VALUES (NEW.ID,'VERIFIED',NOW());
        ELSEIF NEW.verified = '0' THEN
            INSERT INTO user_records (userid, action, timestamp)
            VALUES (NEW.ID,'UNVERIFIED',NOW());
        END IF;
    END IF;
END $$
DELIMITER ;

I also appended

DELIMITER ;

to get the ; back as the normal command delimiter.

MySQL Trigger – How to Create Trigger for Update or Insert in Another Table

First, you need to specified when do you want to run the trigger inside the table. I've edited your trigger and use AFTER UPDATE and AFTER INSERT:

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
    BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
    IF @COUNT=0 THEN
        INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES 
        (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0); 
    ELSE
        UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
        Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
        WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
    END IF;
    END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN

    UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));

END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 1 to avoid duplicate rows.

Change the database TEST.

You can also use ON DUPLICATE KEY on the two triggers execution.

You can get more information in the MySQL's documentation manual.