CODEWITHSUNDEEP
mysqlstored-proceduressyntax-error
Francisco Godienski Hansen
Francisco Godienski Hansen

Reputation: 319

Procedure Syntax Error

I'm having the following errors while trying to create a procedure on MySQL:

Error Code: 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 'insert into objetivo.historico (id_aluno, id_turma, ano, bim1) values (a_id, a_t' at line 5

As well as in a few other lines on the procedure. The thing is that i can't see where am i making a mistake, since some parts of the code that got errors repeat on the procedure without errors.

This is the plain code: 

create procedure objetivo.p_atu_historico (in a_id integer, in a_turma
integer, in a_data date, in a_nota double, in a_bim integer)
begin
if(select count(id) from objetivo.historico where id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y') )= 0 then
    CASE a_bim 
        WHEN 1 THEN (insert into objetivo.historico (id_aluno, id_turma, ano, bim1) values (a_id, a_turma, Format(a_data, '%Y'), a_nota))
        WHEN 2 THEN (insert into objetivo.historico (id_aluno, id_turma, ano, bim2) values (a_id, a_turma, Format(a_data, '%Y'), a_nota))
        WHEN 3 THEN (insert into objetivo.historico (id_aluno, id_turma, ano, bim3) values (a_id, a_turma, Format(a_data, '%Y'), a_nota))
        WHEN 4 THEN (insert into objetivo.historico (id_aluno, id_turma, ano, bim4) values (a_id, a_turma, Format(a_data, '%Y'), a_nota))
        END CASE;

ELSEIF (select count(id) from objetivo.historico where id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y')) <> 0 THEN
    CASE a_bim
        WHEN 1 THEN (UPDATE objetivo.historico SET bim1 = a_nota WHERE id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y'))
        WHEN 2 THEN (UPDATE objetivo.historico SET bim2 = a_nota WHERE id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y'))
        WHEN 3 THEN (UPDATE objetivo.historico SET bim3 = a_nota WHERE id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y'))
        WHEN 4 THEN (UPDATE objetivo.historico SET bim4 = a_nota WHERE id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y'))
    END CASE;
END IF;    
end;

And here's a printscreen of the lines returning errors:

The errors

EDIT

This is what happends when i put the semi-colon in the end of each WHEN statement:

Errors2

Upvotes: 0

Views: 44

Answers (2)

spencer7593
spencer7593

Reputation: 108370

Statements in a stored program need to be terminated with a semicolon.

CASE a_bim 
  WHEN 1 THEN
    insert into ... ;
    --              ^
  WHEN 2 THEN
    ...
END CASE;
--      ^

To create a stored procedure that includes semicolons, you need to change the statement delimiter for the session to some string other than semicolon. This will allow the entire CREATE statement is seen as a single statement. Just be sure whatever delimiter string you choose, that delimiter string only appears at the end of the statement, and doesn't appear anywhere within the CREATE PROCEDURE statement. For example:

DELIMITER $$

CREATE PROCEDURE myproc 
BEGIN
   SET @a := 1;
END$$

DELIMITER ;

EDIT

WHEN isn't a statement, that's part of the CASE statement.

It's the INSERT that is a statement, and that's the statement that needs to be terminated. The parens you have around the INSERT statement are superfluous, they don't serve any purpose. (I don't think that's a syntax error, but I'm not sure about that, I've never tried doing that before... wrapping a statement in unnecessary parens.

In your example, the semicolon has to follow the INSERT statement, which appears inside a paren. That is, if you keep those parens around the statement, that closing paren needs to come after the statement delimiter.

Upvotes: 1

Duffmaster33
Duffmaster33

Reputation: 1190

You have a small syntax error in your procedure. You need to remove the extra parentheses and insert a semicolon after each statement. Like so:

create procedure objetivo.p_atu_historico (in a_id integer, in a_turma
integer, in a_data date, in a_nota double, in a_bim integer)
begin
if(select count(id) from objetivo.historico where id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y') )= 0 then
    CASE a_bim 
        WHEN 1 THEN insert into objetivo.historico (id_aluno, id_turma, ano, bim1) values (a_id, a_turma, Format(a_data, '%Y'), a_nota);
        WHEN 2 THEN insert into objetivo.historico (id_aluno, id_turma, ano, bim2) values (a_id, a_turma, Format(a_data, '%Y'), a_nota);
        WHEN 3 THEN insert into objetivo.historico (id_aluno, id_turma, ano, bim3) values (a_id, a_turma, Format(a_data, '%Y'), a_nota);
        WHEN 4 THEN insert into objetivo.historico (id_aluno, id_turma, ano, bim4) values (a_id, a_turma, Format(a_data, '%Y'), a_nota);
    END CASE;

ELSEIF (select count(id) from objetivo.historico where id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y')) <> 0 THEN
    CASE a_bim
        WHEN 1 THEN UPDATE objetivo.historico SET bim1 = a_nota WHERE id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y');
        WHEN 2 THEN UPDATE objetivo.historico SET bim2 = a_nota WHERE id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y');
        WHEN 3 THEN UPDATE objetivo.historico SET bim3 = a_nota WHERE id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y');
        WHEN 4 THEN UPDATE objetivo.historico SET bim4 = a_nota WHERE id_aluno = a_id and id_turma = a_turma and ano = Format(a_data, '%Y');
    END CASE;
END IF;    
end;

Upvotes: 1

Related Questions