an error with my MySQL script syntax

Question

I'm trying to create a stored function, but an error is appeared which driving me crazy, I really don't know what's the problem with my script, I think there is no error in it, but I don't know why I'm getting this error :

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 'DECLARE cr1 CURSOR FOR SELECT codeArt, qte FROM LigneBonEntrée WHERE numBon = '

And this is my script :

DELIMITER $$

CREATE DEFINER=root@localhost FUNCTION verifierQteDemandee(numBonIn INT) RETURNS BOOLEAN
BEGIN
    DECLARE numLignesBonEntrée, numLignesBonSortie INTEGER;
    DECLARE codeArtLigneBonEntrée, codeArtLigneBonSortie, qteLigneBonEntrée, qteLigneBonSortie INTEGER;
    DECLARE no_more_rows BOOLEAN;
    DECLARE qteArticle INTEGER;
    DECLARE test BOOLEAN DEFAULT TRUE;

    SET numLignesBonEntrée =    (SELECT COUNT(*) FROM LigneBonEntrée WHERE numBon = numBonIn);

    SET numLignesBonSortie =    (SELECT COUNT(*) FROM numLignesBonSortie WHERE numBon = (SELECT estLieA FROM LigneBonEntrée WHERE numBon = numBonIn));

    IF numLignesBonEntrée <> numLignesBonSortie THEN
        SET test = FALSE;
    ELSE
        DECLARE cr1 CURSOR FOR  SELECT codeArt, qte FROM LigneBonEntrée WHERE numBon = numBonIn ORDER BY codeArt ASC;

        DECLARE cr2 CURSOR FOR  SELECT codeArt, qte FROM LigneBonSortieWHERE numBon = (SELECT estLieA FROM LigneBonEntréeWHERE numBon = numBonIn) ORDER BY codeArt ASC;

        DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET no_more_rows = TRUE;

        OPEN cr1;
        OPEN cr2;

        the_loop: LOOP
            FETCH cr1 INTO codeArtLigneBonEntrée, qteLigneBonEntrée;
            FETCH cr2 INTO codeArtLigneBonSortie, qteLigneBonSortie;
            IF no_more_rows THEN 
                CLOSE cr;
                LEAVE the_loop;
            END IF;

            IF codeArtLigneBonEntrée <> codeArtLigneBonSortie THEN
                SET test = FALSE;
            END IF;

            IF qteLigneBonEntrée <> qteLigneBonSortie THEN
                SET test = FALSE;
            END IF;

        END LOOP the_loop;
    END IF;
    RETURN test;

END$$

DELIMITER ;

Answer 1

All of your DECLARE's must be at the top of the procedure, before any other logic. Give the following a try.

DELIMITER $$

CREATE DEFINER=root@localhost FUNCTION verifierQteDemandee(numBonIn INT) RETURNS BOOLEAN
BEGIN
    DECLARE numLignesBonEntrée, numLignesBonSortie INTEGER;
    DECLARE codeArtLigneBonEntrée, codeArtLigneBonSortie, qteLigneBonEntrée, qteLigneBonSortie INTEGER;
    DECLARE no_more_rows BOOLEAN;
    DECLARE qteArticle INTEGER;
    DECLARE test BOOLEAN DEFAULT TRUE;

    -- Moved declarations to before other logic
    DECLARE cr1 CURSOR FOR  SELECT codeArt, qte FROM LigneBonEntrée WHERE numBon = numBonIn ORDER BY codeArt ASC;
    DECLARE cr2 CURSOR FOR  SELECT codeArt, qte FROM LigneBonSortieWHERE numBon = (SELECT estLieA FROM LigneBonEntréeWHERE numBon = numBonIn) ORDER BY codeArt ASC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET no_more_rows = TRUE;

    SET numLignesBonEntrée =    (SELECT COUNT(*) FROM LigneBonEntrée WHERE numBon = numBonIn);

    SET numLignesBonSortie =    (SELECT COUNT(*) FROM numLignesBonSortie WHERE numBon = (SELECT estLieA FROM LigneBonEntrée WHERE numBon = numBonIn));

    IF numLignesBonEntrée <> numLignesBonSortie THEN
        SET test = FALSE;
    ELSE

        OPEN cr1;
        OPEN cr2;

        the_loop: LOOP
            FETCH cr1 INTO codeArtLigneBonEntrée, qteLigneBonEntrée;
            FETCH cr2 INTO codeArtLigneBonSortie, qteLigneBonSortie;
            IF no_more_rows THEN 
                CLOSE cr;
                LEAVE the_loop;
            END IF;

            IF codeArtLigneBonEntrée <> codeArtLigneBonSortie THEN
                SET test = FALSE;
            END IF;

            IF qteLigneBonEntrée <> qteLigneBonSortie THEN
                SET test = FALSE;
            END IF;

        END LOOP the_loop;
    END IF;
    RETURN test;

END$$

DELIMITER ;

Mysql documentation : http://dev.mysql.com/doc/refman/5.0/en/declare.html

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.