mysql 1064 存储过程_MySQL存储过程报错Error Code:1064的解决办法

MySQL存储过程例子,不能在if else里面用begin end否则会报错Error Code:1064解决

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

报错是因为mysql的procedure里面if else语句里面, 用了begin end语句,去掉了就OK了.

[sql]

DELIMITER $$

USE `sportgbmj`$$

DROP PROCEDURE IF EXISTS `sp_web_addmiddayrank`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_web_addmiddayrank`()

BEGIN

/*

修订记录：

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

版本    修订人     修订日期

修订描述

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

1.0.0

2013-06-15    查询当前中午12:30-13:30比赛排名

该sp 当前比赛结束后执行

排名先决条件：

1：在一场内必须完满10(含)局以上

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

*/

DECLARE i_date      DATE ;

DECLARE i_starttime TIME;

DECLARE i_endtime   TIME;

SET i_date    = NOW();

SET i_endtime = NOW();

IF (i_endtime > '22:30') THEN

style="color:#ff0000;">BEGIN

/* 夜间赛场 */

SET i_starttime =

'21:30';

SET i_endtime   =

'22:30';

style="color:#ff0000;">END

ELSE

style="color:#ff0000;">BEGIN

/* 中午场 */

SET i_starttime =

'12:30';

SET i_endtime   =

'13:30';

style="color:#ff0000;">END

END IF;

CREATE TEMPORARY TABLE tmp_rank(

id INT NOT NULL AUTO_INCREMENT -- 自增

,userid INT

-- 用户标识

,beans INT

-- bean 汇总

,rounds TINYINT

-- 局数 汇总

,posttime TIME

-- 时间 (该玩家本场比赛最后的时间)

) 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 ('1001') AND (postdate =

i_date AND posttime >='12:30:00' AND posttime <='13:30:00')

GROUP BY

userid

HAVING

(rounds >= 10)

ORDER BY

beans DESC

,maxtime ASC

LIMIT 500;

/*

插入排名表

*/

INSERT INTO

hf_playranklist

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

SELECT

userid,beans,rounds,i_date,posttime

FROM tmp_rank;

/*

插入奖品表

*/

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 ;