Mysql – declare keyword error thesql

MySQLsyntax

This is the code I've written:

delimiter //
create procedure `update_pts`()
begin

set @ctr=1;
set @cnt=(select count(*) from participant);

declare cs1 cursor for select sap from participant;

open cs1

while @ctr<@cnt do

fetch next from cs1 into @sap

set @points=(select sum(players.points) from players where pname in (select player1,player2,player3,player4,player5,player6,player7,player8 from team where sap=@sap));
set @pointsc=(select points from players where sport='cricket' and dept=(select cdept from team where sap=@sap) and year=(select cyear from team where sap=@sap)); 
set @pointschess=(select points from players where sport='chess' and dept=(select chessdept from team where sap=@sap) and year=(select chessyear from team where sap=@sap));
set @pointstt=(select points from players where sport='tt' and dept=(select ttdept from team where sap=@sap) and year=(select ttyear from team where sap=@sap));
set @pointst=(select points from players where sport='throwball' and dept=(select tdept from     team where sap=@sap) and year=(select tyear from team where sap=@sap));
set @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

update participant set points = @pointsf where sap=@sap;

set @ctr=@ctr+1;

end while;

close cs1;
deallocate cs1;
end //;

MySQL gives me an error on the declare keyword saying 'syntax error, unexpected DECLARE_SYM'.
I tried changing the order of my statements by putting the 2 set statements

set @ctr=1;
set @cnt=(select count(*) from participant);

after the declare statement but then MySQL gives me an error in the while statement saying 'syntax error, unexpected WHILE_SYM, expecting ';''

Can anyone please explain to me why this is happening and how do i fix it?

Thanks

Updated code @Abdul:

delimiter //
create procedure `update_pts`()
begin


 DECLARE noMore BOOLEAN DEFAULT FALSE;   

declare cs1 cursor for select sap from participant;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = TRUE;
#set @ctr=1;
#set @cnt=(select count(*) from participant);
open cs1;

#while @ctr<=@cnt do
REPEAT
fetch cs1 into @sap;
if not noMore then
set @points=(select sum(players.points) from players where pname in (select player1 from     team where sap=@sap
                    union all
                    select player2
                    from team where sap=@sap
                    union all
                    select player3
                    from team where sap=@sap
                    union all
                    select player4
                    from team where sap=@sap
                    union all
                    select player5
                    from team where sap=@sap
                    union all
                    select player6
                    from team where sap=@sap
                    union all
                    select player7
                    from team where sap=@sap
                    union all
                    select player8
                    from team where sap=@sap));
set @pointsc=(select points from players where sport='cricket' and dept=(select cdept     from team where sap=@sap) and year=(select cyear from team where sap=@sap)); 
set @pointschess=(select points from players where sport='chess' and dept=(select     chessdept from team where sap=@sap) and year=(select chessyear from team where sap=@sap));
set @pointstt=(select points from players where sport='tt' and dept=(select ttdept from     team where sap=@sap) and year=(select ttyear from team where sap=@sap));
set @pointst=(select points from players where sport='throwball' and dept=(select tdept     from team where sap=@sap) and year=(select tyear from team where sap=@sap));
set @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

update participant set points = @pointsf where sap=@sap;
end if;
#set @ctr=@ctr+1;
until noMore
end repeat;

close cs1;
#deallocate cs1;
end //

Best Answer

I believe that your syntax is not correct can you please follow this syntax 

DROP PROCEDURE IF EXISTS update_pts;
DELIMITER //

CREATE PROCEDURE `update_pts`()
BEGIN

DECLARE noMore BOOLEAN DEFAULT FALSE;   
DECLARE cursorsap VARCHAR(255);
DECLARE cs1 CURSOR FOR SELECT sap FROM participant;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = TRUE;

SET @ctr=1;
SELECT count(*) INTO @cnt FROM participant;

OPEN cs1;

REPEAT
FETCH cs1 INTO cursorsap;
    IF NOT noMore AND (@ctr < @cnt) THEN
        SELECT sum(players.points) INTO @points FROM players WHERE pname in 
        (
        SELECT player1 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player2 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player3 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player4 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player5 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player6 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player7 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player8 FROM team WHERE sap=cursorsap
        );

        SELECT points INTO @pointsc FROM players WHERE sport='cricket' AND dept=(SELECT cdept FROM team WHERE sap=cursorsap) AND year=(SELECT cyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointschess FROM players WHERE sport='chess' AND dept=(SELECT chessdept FROM team WHERE sap=cursorsap) AND year=(SELECT chessyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointstt FROM players WHERE sport='tt' AND dept=(SELECT ttdept FROM team WHERE sap=cursorsap) AND year=(SELECT ttyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointst FROM players WHERE sport='throwbALL' AND dept=(SELECT tdept FROM team WHERE sap=cursorsap) AND year=(SELECT tyear FROM team WHERE sap=cursorsap);

        SET @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

        update participant SET points = @pointsf WHERE sap=cursorsap;

        SET @ctr=@ctr+1;
    END IF;
UNTIL noMore
END repeat;
CLOSE cs1;
END //

Please use this updated code let me know if you still have issues.

Related Question