using "if" and "else" Stored Procedures MySQL

Question

I'm having some difficulties when trying to create this stored procedure, any kind of help is welcome:

create procedure checando(in nombrecillo varchar(30), in contrilla varchar(30), out resultado int)  begin   if exists (select * from compas where nombre = nombrecillo and contrasenia = contrilla) then     set resultado = 0; else if exists (select * from compas where nombre = nombrecillo) then     set resultado = -1; else      set resultado = -2; end if; end; 

The table I'm working on is:

+-------------+-------------+------+-----+---------+-------+ | Field       | Type        | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | Nombre      | varchar(30) | YES  |     | NULL    |       | | contrasenia | varchar(30) | YES  |     | NULL    |       | +-------------+-------------+------+-----+---------+-------+ 

Answer 1

The problem is you either haven't closed your if or you need an elseif:

create procedure checando(     in nombrecillo varchar(30),     in contrilla varchar(30),      out resultado int) begin       if exists (select * from compas where nombre = nombrecillo and contrasenia = contrilla) then         set resultado = 0;     elseif exists (select * from compas where nombre = nombrecillo) then         set resultado = -1;     else          set resultado = -2;     end if; end; 

Answer 2

I think that this construct: if exists (select... is specific for MS SQL. In MySQL EXISTS predicate tells you whether the subquery finds any rows and it's used like this: SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

You can rewrite the above lines of code like this:

DELIMITER $$  CREATE PROCEDURE `checando`(in nombrecillo varchar(30), in contrilla varchar(30), out resultado int)  BEGIN     DECLARE count_prim INT;     DECLARE count_sec INT;      SELECT COUNT(*) INTO count_prim FROM compas WHERE nombre = nombrecillo AND contrasenia = contrilla;     SELECT COUNT(*) INTO count_sec FROM FROM compas WHERE nombre = nombrecillo;      if (count_prim > 0) then         set resultado = 0;     elseif (count_sec > 0) then         set resultado = -1;     else          set resultado = -2;     end if;     SELECT resultado; END