Using "if" and "else" in MySQL Stored Procedures

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    |       |
+-------------+-------------+------+-----+---------+-------+

Bohemian · Accepted Answer · 2016-03-30 16:29:36Z

43

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;

edited Mar 30, 2016 at 16:29

answered Jun 13, 2011 at 2:17

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Octavian Vladu · Accepted Answer · 2012-04-18 19:10:04Z

17

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

edited Apr 18, 2012 at 19:10

answered Apr 18, 2012 at 18:50

Octavian Vladu

1,26013 silver badges16 bronze badges

1 Comment

Octavian Vladu Over a year ago

@Bohemian said that you can use elif. I wonder where someone can find more information about elif structure in MySQL. Bash?

Extreme · Accepted Answer · 2018-04-03 20:52:56Z

2

you can use CASE WHEN as follow as achieve the as IF ELSE.

SELECT FROM A a 
LEFT JOIN B b 
ON a.col1 = b.col1 
AND (CASE 
        WHEN a.col2 like '0%' then TRIM(LEADING '0' FROM a.col2)
        ELSE substring(a.col2,1,2)
    END
)=b.col2;

p.s:just in case somebody needs this way.

answered Apr 3, 2018 at 20:52

Extreme

3,4991 gold badge21 silver badges30 bronze badges

Collectives™ on Stack Overflow

Using "if" and "else" in MySQL Stored Procedures

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related