Menu
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 | | +-------------+-------------+------+-----+---------+-------+ 
965
The IF ELSE statement controls the flow of execution in SQL Server. It can be used in stored-procedures, functions, triggers, etc. to execute the SQL statements based on the specified conditions. Boolean_expression: A boolean expression that returns TRUE or FALSE.
The syntax for the IF-THEN-ELSE statement in MySQL is: IF condition1 THEN {... statements to execute when condition1 is TRUE...} [ ELSEIF condition2 THEN {...
The MySQL IF() function is used for validating a condition. The IF() function returns a value if the condition is TRUE and another value if the condition is FALSE. The MySQL IF() function can return values that can be either numeric or strings depending upon the context in which the function is used.
Each procedure has one or more statements. In our case, these are SQL statements. So, you can write a procedure that will – insert new data, update or delete existing, retrieve data using the SELECT statement. And even better, you can combine more (different statements) in the stored procedures.
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; 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
