MySQL stored procedure example. you cannot use beginend in ifelse. Otherwise, the error Erro_MySQL will be reported.

MySQL stored procedure example. you cannot use beginend in ifelse. Otherwise, the error ErrorCode: 1064 is reported to solve bitsCN.com.

MySQL stored procedure example. you cannot use begin end in if else. Otherwise, an Error Code: 1064 will be reported.

Error Code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

The error is reported because the if else statement in mysql procedure uses the begin end statement. if it is removed, it is OK.

[SQL]

DELIMITER $

USE 'sportgbmj '$

Drop procedure if exists 'sp _ web_addmiddayrank '$

Create definer = 'root' @ '%' PROCEDURE 'sp _ web_addmiddayrank '()

BEGIN

/*

Revision history:

-------------------------------------------------------------------

Version modifier revision date revision description

-------------------------------------------------------------------

1.0.0

This sp is executed after the current competition ends

Prerequisites:

1: within the same venue, 10 or more bureaus must be completed.

-------------------------------------------------------------------

*/

DECLARE I _date DATE;

DECLARE I _starttime TIME;

DECLARE I _endtime;

SET I _date = NOW ();

SET I _endtime = NOW ();

IF (I _endtime> '22: 30') THEN

BEGIN

/* Night Arena */

SET I _starttime = '21: 30 ';

SET I _endtime = '22: 30 ';

END

ELSE

BEGIN

/* Midday venue */

SET I _starttime = '12: 30 ';

SET I _endtime = '13: 30 ';

END

End if;

Create temporary table tmp_rank (

Id int not null AUTO_INCREMENT -- Auto-increment

, Userid INT -- user ID

, Beans INT -- bean summary

, Rounds TINYINT -- Number of bureaus

, Posttime TIME -- TIME (the last TIME of the player's game)

) ENGINE = MYISAM;

Insert into tmp_rank (userid, beans, rounds, posttime)

SELECT

Userid

, SUM (case when consume> 0 THEN consume ELSE 0 END) beans

, COUNT (userid) rounds

, MAX (posttime) AS posttime

FROM

Score AS S

INNER JOIN

Scorelist AS SL

ON

S. pid = SL. pid

WHERE

Roomid IN ('20140901') AND (postdate = I _date AND posttime> = '12: 30: 00' AND posttime <= '13: 30: 00 ')

GROUP

Userid

HAVING

(Rounds> = 10)

ORDER

Beans DESC

, Maxtime ASC

LIMIT 500;

/*

Insert a table

*/

INSERT

Hf_playranklist (userid, rank, beans, rounds, postdate, posttime)

SELECT

Userid, beans, rounds, I _date, posttime

FROM tmp_rank;

/*

Insert prize table

*/

Insert into hf_prizelist (userid, STATUS, prizeid, receivetime, postdate)

SELECT

Userid

, 0

, (Case when id> = 11 THEN 4

WHEN (id> = 3 AND id <= 10) THEN 3

WHEN id = 2 THEN 2

WHEN id = 1 THEN 1

END) AS prizeid

, Beans

, Rounds

, Posttime

FROM

Tmp_rank AS R

WHERE

Rank> = 30;

END $

DELIMITER;

BitsCN.com