Bug #33202 function not running giving 1064 error please help

• Submitted: 13 Dec 2007 7:55
• Modified: 13 Dec 2007 15:10
• Reporter: ani ani
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• OS: Windows
• CPU Architecture: Any
• Tags: FUNCTION

[13 Dec 2007 7:55] ani ani

Description:
DELIMITER $$
 DROP FUNCTION IF EXISTS `mcams`.`getSumRuns` $$
CREATE FUNCTION `mcams`.`getSumRuns`(playerID VARCHAR(85),tourID VARCHAR(25))
RETURNS INTEGER(10)
DECLARE  totalRuns INTEGER(10) $$
BEGIN

  SELECT sum(A.Runs) into totalRuns FROM tourbattingscoresheet A,playermaster B WHERE tournamentCategoryId in (select       tournamentCategoryId FROM tournamentcategorymaster  WHERE tournamentid = tourID) and    A.runs!= -1 and A.runs!= 0 and  A.batsmanId=playerID and  B.playerid=A.batsmanId   group by A.batsmanId  order by sum(A.runs) desc$$
  RETURN totalRuns$$

END $$

DELIMITER ;

How to repeat:
run the function

[13 Dec 2007 12:01] Peter Laursen

try

DELIMITER $$

DROP FUNCTION IF EXISTS `mcams`.`getSumRuns` $$

CREATE FUNCTION `mcams`.`getSumRuns`(playerID VARCHAR(85),tourID VARCHAR(25))
RETURNS INTEGER(10)

BEGIN
DECLARE  totalRuns INTEGER(10) ;
SELECT sum(A.Runs) into totalRuns FROM tourbattingscoresheet A,playermaster B WHERE
tournamentCategoryId in 
(select tournamentCategoryId FROM tournamentcategorymaster 
WHERE tournamentid = tourID) 
and    
A.runs!= -1 and A.runs!= 0 and  A.batsmanId=playerID and  B.playerid=A.batsmanId   

group by A.batsmanId  
order by sum(A.runs) desc;

RETURN totalRuns;

END $$

DELIMITER ;

Points: 
1) DECLARE after BEGIN
2) "INSIDE" the Create Function statement use ";" as delimiter.  Using the user defined DELIMITER "INSIDE" the Create Function statement 'breaks' the statement.

(posted by a non-mysql person)

[13 Dec 2007 12:16] ani ani

thanks for you support sir

[13 Dec 2007 15:10] Susanne Ebrecht

Many thanks for choosing MySQL.
Because the solution is given before, I'll close this bug now.
If this is not ok for you, please, open it again and let us know the issue.