MySQL - Using IF EXISTS seems to be breaking my statement

Question

I have a stored routine that is meant to create a new desktop login token for my application, that verifies if the user has to relogin (anyway that's not important).

What is important is that the SQL I'm using doesn't seem to like me.

I only ever use T-SQL because I'm a .NET developer who focuses with SSMS for databases, but this time I stupidly decided to use MySQL with no experience XD

Here's the code:

CREATE PROCEDURE `insertNewToken`(IN `Username` VARCHAR(150), IN `Token` VARCHAR(500))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''

IF EXISTS(SELECT *
                FROM desktopTokens
                WHERE desktopTokens.AssignedUsername = Username) THEN
    BEGIN
        UPDATE desktopTokens
        SET desktopTokens.TokenValue = Token
        WHERE desktopTokens.AssignedUsername = Username
    END
ELSE
    BEGIN
        INSERT INTO desktopTokens
        VALUES
        (Username, Token)
    END

I keep getting errors at LINE 2 and LINE 15 but have no idea why!

NOTE: Token and Username are SP variables

How you execute this code makes a difference --Is this code inside a `CREATE PROCEDURE` block? Please post all of the surrounding context. MySQL does not allow the use of `IF/ELSE, BEGIN/END` in regular SQL statements outside of a `CREATE PROCEDURE / FUNCTION`. — Michael Berkowski, Aug 26 '16 at 21:16
Related, if not a duplicate: [Usage of MySQL's “IF EXISTS”](http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists) — Wondercricket, Aug 26 '16 at 21:17
If AssgnedUsername is unique in table use INSERT ON DUPLICATE KEY UPDATE. like this http://stackoverflow.com/questions/39139181/mysql-insert-record-if-it-is-already-exists-update-record/39140725#39140725 — Mike, Aug 26 '16 at 21:24
The use of `BEGIN`/`END` looks wrong to me. Usually `BEGIN`/`END` is wrapped around the function body, not inside if/else blocks. IF/THEN/ELSE IF/`END IF`. — ebyrob, Aug 26 '16 at 21:25
http://stackoverflow.com/questions/11427457/what-is-the-right-syntax-of-if-statement-in-mysql — Jim Hewitt, Aug 26 '16 at 21:32

score 0 · Accepted Answer · answered Aug 26 '16 at 21:29

0

Try:

DELIMITER $$
CREATE PROCEDURE `insertNewToken`(
    IN `Username` VARCHAR(150), 
    IN `Token` VARCHAR(500)
)
BEGIN
    IF EXISTS(SELECT *
            FROM desktopTokens
            WHERE desktopTokens.AssignedUsername = Username) THEN

      UPDATE desktopTokens
      SET desktopTokens.TokenValue = Token
      WHERE desktopTokens.AssignedUsername = Username
      ;
    ELSE
      INSERT INTO desktopTokens
      VALUES
      (Username, Token)
      ;
    END IF;
END $$
DELIMITER ;

answered Aug 26 '16 at 21:29

ebyrob

667
7
24

Yeah that created it. Haven't tried inserting data yet but I assume that will work. I'll also port my project over to SQL Server because I realised why I don't like MySQL :) thanks anyway – Astrify Aug 26 '16 at 21:31

MySQL - Using IF EXISTS seems to be breaking my statement

1 Answers1