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 | |
+-------------+-------------+------+-----+---------+-------+
This question is related to
mysql
database
stored-procedures
if-statement
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
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.
Source: Stackoverflow.com