Asuka K Asuka K - 3 months ago 17
MySQL Question

MySQL - Using IF EXISTS seems to be breaking my statement

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

Answer

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 ;