mySQL Create Procedure error + general mysql help

Question

Hi I'm very very new at MySQL and was wondering if anyone could help me out. I'm trying to build a procedure for my database and am using the following code.

CREATE PROCEDURE `createuser`(username VARCHAR(100), password VARCHAR(100), email VARCHAR(100)) BEGIN

DECLare returnValue int;

IF EXISTS(SELECT 'True' FROM User_Table WHERE(User_Username = username OR User_Email = email))  
    BEGIN     
        SET returnValue = 0;
    END; 
ELSE
    BEGIN
       SET returnValue = 1;
       INSERT into User_Table(User_Username, User_Password,User_Email) VALUES(username, password,email) 
    END; END;

I'm getting a error on the BEGIN after the if statement. The error is "Bad syntax near "BEGIN SET returnvalue = 0; END ELSE;

Could you please tell me if im using the IF statement correctly in a mysql sence. It seems to work fine on a MSSql server but not on a Mysql.

Would it also be possible to point me in the direction of some good tutorials for this kinda stuff as the main MYSQL website isn't particularity user friendly.

Cheers

Answer 1

Instead of

IF statement
    BEGIN
        commands;
    END
ELSE
    BEGIN
        commands;
    END

use

IF statement THEN
    commands;
ELSE
    commands;
END IF;

Here is how I would write your procedure (changes may be a mixture of cosmetic changes and actual changes; suggest experimenting to find out which are which)

CREATE PROCEDURE `createuser` ( IN username VARCHAR(100),
                                IN password VARCHAR(100),
                                IN email VARCHAR(100)
                                )
BEGIN
    DECLARE returnValue INT;
    IF EXISTS (
        SELECT 1
        FROM User_Table
        WHERE User_Username = username OR User_Email = email
    ) THEN
        SET returnValue = 0;
    ELSE
        SET returnValue = 1;
        INSERT INTO User_Table
        (User_Username, User_Password, User_Email)
        VALUES
        (username, password, email);
    END IF;
END;

You don't appear to be doing anything with the variable returnValue. You might need to declare this as OUT or INOUT, depending on what you want to do with it. I haven't got experience of using return values for stored procedures.