Error in MySql Stored Procedure

Question

Error in MySql Stored Procedure

Navigation

#1 by (2 votes)

1

I am new trying to work with stored procedures in MySql this SP that I try to create next is to verify the access of a user to the system ...

DROP PROCEDURE IF EXISTS verificarUsuarios; 
DELIMITER //
    CREATE PROCEDURE verificarUsuarios(IN _email VARCHAR(55), IN _pass VARCHAR(55), OUT _validacion BIT)
    BEGIN
        DECLARE @_resultado INT DEFAULT 0;
        SET _validacion = 0;        
        SELECT @_resultado := COUNT(*) FROM usuarios WHERE email = _email AND password = _pass;
        IF @_resultado > 0 THEN
            SET _validacion = 1;
        ELSE
            SET _validacion = 0;
        END IF
    END
//

The error I get is the following:

#1064 - Algo está equivocado en su sintaxis cerca de '@resultado;
    SET _validacion = 0;
    SELECT @_resultado := COUNT(*) FROM us' en la linea 3

mysql

asked by antonio291093 08.12.2017 в 21:23

source

1 answer

Correct way to send data to a view with jpa spring framework Replace a text part in MYSQL table

score 2 · Accepted Answer

Local variable names to a procedure can not start with @ . So the variable for the result should be declared in this way (the DEFAULT is not really necessary either):

DECLARE _resultado INT;

Then, to be able to assign the result of the query to that local variable, you must use the clause INTO :

SELECT COUNT(*) into _resultado FROM usuarios ...

And finally, additional details:

You are missing a semicolon after END IF
The SET _validacion = 0; is redudante. You can remove it.

The procedure would be as follows:

DROP PROCEDURE IF EXISTS verificarUsuarios; 
DELIMITER //
    CREATE PROCEDURE verificarUsuarios(IN _email VARCHAR(55), IN _pass VARCHAR(55), OUT _validacion BIT)
    BEGIN
        DECLARE _resultado INT;
        SELECT COUNT(*) into _resultado FROM usuarios WHERE email = _email AND password = _pass;
        IF _resultado > 0 THEN
            SET _validacion = 1;
        ELSE
            SET _validacion = 0;
        END IF;
    END
//
DELIMITER ;

Edit

It may be worth converting the procedure to a function to be able to execute it with a more convenient syntax:

DELIMITER //
    CREATE FUNCTION verificarUsuarios(_email VARCHAR(55), _pass VARCHAR(55)) RETURNS BIT
    BEGIN
        DECLARE _resultado INT;
        SELECT COUNT(*) into _resultado FROM usuarios WHERE email = _email AND password = _pass;
        IF _resultado > 0 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END
//
DELIMITER ;

Then you can call them like this:

SET @resultadoValidacion = verificarUsuarios("[email protected]","passuser");