CODEWITHSUNDEEP
mysqlsql
David Castro
David Castro

Reputation: 738

Using variables on stored procedures in MySQL

I'm trying to make this stored procedure work; I'm trying to receive a parameter and with that look for a row in 'participantes' table, counting the result to validate if the id exists in the table. Somehow when I try to save the procedure it fails sayin "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 'END' at line 16". Any help will be appreciatted.

BEGIN
DECLARE cuentaAtleta INT;

SELECT COUNT(*)
INTO cuentaAtleta 
FROM participantes
WHERE id_participante =  id; /* id is a parameter */

IF cuentaAtleta > 0 THEN
    SELECT *
    FROM participantes
    INNER JOIN disciplinas ON participantes.id_disciplina = disciplinas.id_disciplina
    WHERE id_participante =  'id;

END IF
END

It's on a Linux Server
MySQL Version: 5.1.68-cll
PHP Version: 5.3.16

Upvotes: 1

Views: 1617

Answers (1)

John Woo
John Woo

Reputation: 263723

Because you haven't terminated the statements and you haven't change the delimiter. 

DELIMITER $$
CREATE PROCEDURE procNameHERE(IN ID INT)
BEGIN

    DECLARE cuentaAtleta INT;

    SELECT  COUNT(*)
    INTO    cuentaAtleta 
    FROM    participantes
    WHERE   id_participante =  id;

    IF cuentaAtleta > 0 THEN
        SELECT  *
        FROM    participantes
                INNER JOIN disciplinas 
                    ON participantes.id_disciplina = disciplinas.id_disciplina
        WHERE   id_participante =  id;
    END IF ;

END $$
DELIMITER ;

Upvotes: 3

Related Questions