Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 //
share|improve this question
    
Why are you setting anything before the cursor declaration.? Also why are you deallocating any cursor as deallocate cs1; Please see my answer and try to follow the syntax i used –  Abdul Manaf Feb 7 '13 at 12:26

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
what is repeat? it is not showing as a keyword and i got an error on the fetch statement when i tried out your query –  Binoy Dalal Feb 7 '13 at 12:38
    
i am still getting an error in the fetch syntax mysql says to check the syntax near @sap any ideas? –  Binoy Dalal Feb 7 '13 at 13:05
    
and the set query inside the loop works...just tried it out –  Binoy Dalal Feb 7 '13 at 13:12
    
@BinoyDalal: use my updated code –  Abdul Manaf Feb 7 '13 at 13:33
    
Still not fetching any rows...and my logic is 100% cause when i tried it out manually it worked. you got any other ideas? –  Binoy Dalal Feb 7 '13 at 13:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.