Using "if" and "else" in MySQL Stored Procedures
110,054
Solution 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;
Solution 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
Solution 3
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.
Related videos on Youtube
Author by
Raúl Núñez Cuevas
Updated on July 09, 2022Comments
-
Raúl Núñez Cuevas almost 2 years
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 | | +-------------+-------------+------+-----+---------+-------+
-
roy about 11 years+1 por los nombrecillos
-
-
Octavian Vladu about 12 years@Bohemian said that you can use elif. I wonder where someone can find more information about elif structure in MySQL. Bash?