CODEWITHSUNDEEP
mysqlmariadb
Mihai
Mihai

Reputation: 25

Mysql Procedure IF,ELSEIF Statment error

I try to create a procedure in mysql, but i get an syntax error, i don`t know why i get it and where is ''

# 1064 - You have an error in the RSQL syntax next to '' on line 4

My Procedure:

CREATE PROCEDURE MUWAP_VipAdd(IN szCharName varchar(10),IN DayAdd int,IN VipType smallint,IN datenow_srv datetime)
BEGIN
IF NOT EXISTS (SELECT 1 FROM `T_VIPList` WHERE AccountID = szCharName) THEN
INSERT INTO `T_VIPList` (`AccountID`,`Date`,`Type`) VALUES (szCharName,DATE_ADD(datenow_srv, INTERVAL DayAdd DAY),VipType);
ELSE IF EXISTS (SELECT 1 FROM `T_VIPList`  WHERE AccountID = szCharName AND Date > datenow_srv) THEN
UPDATE `T_VIPList` SET `Date` = DATE_ADD(`Date`,INTERVAL DayAdd DAY) WHERE `AccountID` = szCharName;
UPDATE `T_VIPList` SET `Type` = VipType WHERE `AccountID` = szCharName AND `Type` < VipType;
ELSE 
UPDATE `T_VIPList` SET `Date` = DATE_ADD(`Date`,INTERVAL DayAdd DAY), `Type` = VipType WHERE `AccountID` = szCharName;
END IF
END

Thank you!

Upvotes: 0

Views: 900

Answers (1)

Barmar
Barmar

Reputation: 781105

In order to have multiple statements in a THEN statement, you need to use BEGIN...END.

CREATE PROCEDURE MUWAP_VipAdd(IN szCharName varchar(10),IN DayAdd int,IN VipType smallint,IN datenow_srv datetime)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM `T_VIPList` WHERE AccountID = szCharName) 
    THEN INSERT INTO `T_VIPList` (`AccountID`,`Date`,`Type`) VALUES (szCharName,DATE_ADD(datenow_srv, INTERVAL DayAdd DAY),VipType);
    ELSE IF EXISTS (SELECT 1 FROM `T_VIPList`  WHERE AccountID = szCharName AND Date > datenow_srv) 
    THEN BEGIN
        UPDATE `T_VIPList` SET `Date` = DATE_ADD(`Date`,INTERVAL DayAdd DAY) WHERE `AccountID` = szCharName;
        UPDATE `T_VIPList` SET `Type` = VipType WHERE `AccountID` = szCharName AND `Type` < VipType;
    END
    ELSE UPDATE `T_VIPList` SET `Date` = DATE_ADD(`Date`,INTERVAL DayAdd DAY), `Type` = VipType WHERE `AccountID` = szCharName;
    END IF
END

You could also combine the two UPDATE queries into a single query:

UPDATE T_VIPList
SET Date = DATE_ADD(Date, INTERVAL DayAdd DAY),
    Type = GREATEST(VipType, Type)
WHERE AccountID = szCharName;

In fact, it seems like the whole thing could be done with a single INSERT ... ON DUPLICATE KEY UPDATE query:

INSERT INTO T_VIPList (AccountID, Date, Type)
VALUES (szCharName,DATE_ADD(datenow_srv, INTERVAL DayAdd DAY),VipType)
ON DUPLICATE KEY UPDATE
    Date = IF(Date > datenow_srv, VALUES(Date), Date),
    Type = GREATEST(Type, VALUES(Type));

Upvotes: 1

Related Questions