MySql - if exists clause in stored procedure

Question

I'm working on login/register module of my program based on a tutorial I found. Everything worked fine but then I switched from MSSql to MySql database.

The problem is I can't rewrite my InsertUser procedure. This procedure takes 3 parameters (username, password and email) and can return 3 different values

-1 if username is already used
-2 if email is already used
id of new row if registration is successful

I tried to write it like this:

DELIMITER $$
CREATE PROCEDURE InsertUser(
    IN username VARCHAR(50),
    IN pass VARCHAR(255),
    IN email VARCHAR(50))
BEGIN
IF (SELECT Id FROM Users WHERE UserName=username) THEN
BEGIN
    SELECT -1;
END;
ELSEIF (SELECT Id FROM Users WHERE Email=email)
THEN
BEGIN
    SELECT -2;
END;
ELSE
BEGIN
    INSERT INTO Users (UserName, Password, RegDate, Email) VALUES(username, pass, CURDATE(), email);
    SELECT LAST_INSERT_ID();
END;
END IF;
END $$
DELIMITER ;

When I try to create this procedure using the above code from Visual Studio I get error (wrong syntax). However there is no error if I do it from phpmyadmin page. But it doesn't work at all (returns nothing) regardless of arguments I provide. I'm using phpmyadmin page and execute procedure option to test it.

Here is original T-Sql code:

CREATE PROCEDURE [dbo].[Insert_User]    
      @Username NVARCHAR(20),    
      @Password NVARCHAR(20),    
      @Email NVARCHAR(50)    
AS    
BEGIN    
      SET NOCOUNT ON;    
      IF EXISTS(SELECT Id FROM Users WHERE Username = @Username)    
      BEGIN    
            SELECT -1 -- Username exists.    
      END    
      ELSE IF EXISTS(SELECT Id FROM Users WHERE Email = @Email)    
      BEGIN    
            SELECT -2 -- Email exists.    
      END    
      ELSE    
      BEGIN    
            INSERT INTO [Users]    
                     ([Username]    
                     ,[Password]    
                     ,[Email]    
                     ,[RegDate])    
            VALUES    
                     (@Username    
                     ,@Password    
                     ,@Email    
                     ,GETDATE())                       
            SELECT SCOPE_IDENTITY() -- UserId 
     END    
END

Any ideas?

Avoid naming variables and parameter as columns of your tables. — wchiquito
– wchiquito, Commented Nov 25, 2015 at 13:22

wchiquito · Accepted Answer · 2015-11-25 14:02:03Z

1

6.10.1 Using Stored Routines from Connector/Net

...

Unlike the command-line and GUI clients, you are not required to specify a special delimiter when creating stored procedures in Connector/Net.

...

One option in MySQL (command-line):

DELIMITER $$

CREATE PROCEDURE `InsertUser` (
    IN `_username` VARCHAR(50),
    IN `_pass` VARCHAR(255),
    IN `_email` VARCHAR(50)
)
BEGIN
    IF (SELECT `Id` FROM `Users` WHERE `UserName` = `_username`) THEN
        SELECT -1;
    ELSEIF (SELECT `Id` FROM `Users` WHERE `Email` = `_email`) THEN
        SELECT -2;
    ELSE
        INSERT INTO `Users` (`UserName`, `Password`, `RegDate`, `Email`)
        VALUES (`_username`, `_pass`, CURDATE(), `_email`);
        SELECT LAST_INSERT_ID();
    END IF;
END$$

DELIMITER ;

answered Nov 25, 2015 at 14:02

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cahir09 Over a year ago

I just realized that our solutions are excactly the same. Thanks

Cahir09 · Accepted Answer · 2015-11-25 14:05:47Z

0

Thank you, but I solved it myself. I don't know why I wanted so badly to use EXISTS function:) I got rid of it and now it works:

BEGIN
    IF(SELECT Id FROM Users WHERE UserName=username) IS NOT NULL THEN
        SELECT -1;
    ELSEIF (SELECT Id FROM Users WHERE Email=email) IS NOT NULL THEN
        SELECT -2;
    ELSE
        INSERT INTO Users(UserName, Password, RegDate, Email) VALUES (username, pass, CURDATE(), email);
        SELECT LAST_INSERT_ID();
    END IF;
END

answered Nov 25, 2015 at 14:05

Cahir09

5116 silver badges14 bronze badges

Collectives™ on Stack Overflow

MySql - if exists clause in stored procedure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related