Unexpected IF in MySQL procedure-Collection of common programming errors


  • OMG Ponies

    Can anyone tell me what is wrong with the following procedure? MySQL says it is an ‘unexpected IF’. I looked up the syntax for an IF statement and I simply don’t see what the problem is.

    create procedure spInsertAccount
    (
    AccountID INT,
    InUse bit,
    IsBanned bit,
    IsPremium bit,
    IsGolden bit,
    Username VARCHAR(20),
    Password VARCHAR(20),
    Email VARCHAR(65),
    LoggedInID INT,
    CreateDate DATETIME
    )
    
        SET @accountExists = (SELECT * FROM Accounts WHERE AccountID = @AccountID);
    
        IF (accountExists > 0) THEN
    
       INSERT IGNORE INTO Accounts(AccountID, InUse, IsBanned, IsPremium, IsGolden, Username, Password,
                            Email, LoggedInID, CreateDate
                           )
            values (@AccountID, @InUse, @IsBanned, @IsPremium, @IsGolden, @Username, @Password,
                   @Email, @LoggedInID, @CreateDate);
            #where NOT EXISTS( SELECT * FROM Accounts WHERE AccountID = @AccountID);
    
        END IF;
    

  • themis

    Try this instead

    Also check out here http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/

    DELIMITER //  
    create procedure `spInsertAccount`
    (
    IN AccountID INT,
    IN InUse bit,
    IN IsBanned bit,
    IN IsPremium bit,
    IN IsGolden bit,
    IN Username VARCHAR(20),
    IN Password VARCHAR(20),
    IN Email VARCHAR(65),
    IN LoggedInID INT,
    IN CreateDate DATETIME
    )
    BEGIN
        SET @accountExists = (SELECT * FROM Accounts WHERE AccountID = @AccountID);
    
        if (accountExists > 0) THEN 
            INSERT IGNORE INTO Accounts(AccountID, InUse, IsBanned, IsPremium, IsGolden, Username, Password,Email, LoggedInID, CreateDate)
            values (@AccountID, @InUse, @IsBanned, @IsPremium, @IsGolden, @Username, @Password,
                   @Email, @LoggedInID, @CreateDate);
            #where NOT EXISTS( SELECT * FROM Accounts WHERE AccountID = @AccountID);
    
        END IF;
    END //