Create procedure with input parameters, and then insert in BD after a validation

Question

Create procedure with input parameters, and then insert in BD after a validation

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

My problem is this, I am trying to create a procedure that receives input parameters and check if the data exists or not, to be able to insert in one table or another. I have tried in different ways and I can not find the error that appears in the syntax, of the declaration.

Please, I need help.

A thousand thanks in advance.

/ Next I show the code of the procedure /

CREATE PROCEDURE InsertMensaje(IN pNOMBRE varchar(50), IN pTLFN varchar(9), IN pEMAIL varchar(50), IN pASN varchar(15), IN pMSG varchar(500))

BEGIN

    /*declaro valiable*/
    DECLARE vIDCLI int;

/*ESTAS OPCIONES TAMBIÉN HE PROBADO*/
/*SET vIDCLI = (select id_cliente from tb_cliente where email = pEMAIL);*/
/*select id_cliente into vIDCLI from tb_cliente where email = pEMAIL;*/

/* DECLARE vAUX varchar(50)*/ 
/* SET vAUX = (select email from tb_cliente where email = pEMAIL);*/
/* select email into vAUX from tb_cliente where email = pEMAIL; */

/* IF (vAUX <=> NULL) THEN  */

    /*compruebo si existe el parámetro pEMAIL*/
    IF EXISTS (select id_cliente from tb_cliente where email = pEMAIL) THEN

    /*recupero el ID y lo asigno a la variable*/
    select id_cliente into vIDCLI from tb_cliente where email = pEMAIL;

        /*inserto en BD tabla mensaje*/
        INSERT INTO 'tb_msg'('asn', 'msg', 'id_cli') VALUES ('pASN','pMSG','vIDCLI');

    ELSE
        /*inserto nuevo cliente*/
        INSERT INTO 'tb_cliente'('nombre', 'tlfn', 'email') 
                        VALUES ('pNOMBRE','pTLFN','pEMAIL');

        /*recupero new IdCli*/
        SET vIDCLI = (select id_cliente from tb_cliente where email = pEMAIL);

        /*insert en la tabla mensaje*/
        INSERT INTO 'tb_msg'('asn', 'msg', 'id_cli') VALUES ('pASN','pMSG',vIDCLI);
    END IF;
END; //

DELIMITER ;

MySQL has said:

1064 - Something is wrong in its syntax near 'IF EXISTS (select id_cliente from tb_cliente where email = pEMAIL) THEN' on line 25

mysql

asked by Miguel 24.09.2018 в 13:02

source

3 answers

0

You could try the condition select if, SELECT IF (500

answered by 24.09.2018 в 13:19

0

It looks like a delimiter problem. Change DELIMITER ; by DELIMITER // and make sure you have all the lines with their respective ; except the end that is ok: END; //

answered by 24.09.2018 в 13:44

How can I install the HAXM? Upload pdf file and send it by mail

score 0 · Accepted Answer

You can actually verify if the email exists within the same stored procedure, I did it with a function because I assumed that within your development you need to check the existence of an email in different procedures, then the function "exists_EMAIL" will allow you to reuse it part of the code.

DELIMITER //
CREATE PROCEDURE InsertMensaje(
    IN pNOMBRE varchar(50), 
    IN pTLFN varchar(9), 
    IN pEMAIL varchar(50), 
    IN pASN varchar(15), 
    IN pMSG varchar(500))

BEGIN

/*declaro valiable*/
DECLARE vIDCLI INT DEFAULT 0;

/*compruebo si existe el parámetro pEMAIL*/
IF 'existe_EMAIL'(pEMAIL) THEN

    /*recupero el ID y lo asigno a la variable*/
    select id_cliente into vIDCLI from tb_cliente where email = pEMAIL;

    /*inserto en BD tabla mensaje*/
    INSERT INTO 'tb_msg'('asn', 'msg', 'id_cli') VALUES (pASN, pMSG, vIDCLI);

ELSE
    /*inserto nuevo cliente*/
    INSERT INTO 'tb_cliente'('nombre', 'tlfn', 'email') 
                    VALUES ('pNOMBRE', 'pTLFN', 'pEMAIL');

    /*recupero new IdCli*/
    /*SET vIDCLI = (select id_cliente from tb_cliente where email = pEMAIL); CODIGO INEFICIENTE*/ 
    SELECT LAST_INSERT_ID() INTO vIDCLI; /* CORRECTO */

    /*insert en la tabla mensaje*/
    INSERT INTO 'tb_msg'('asn', 'msg', 'id_cli') VALUES ('pASN','pMSG', vIDCLI);
END IF;
END; //

DELIMITER ;



/* Funcion que verifica si un email existe en la tabla tb_cliente */
DELIMITER //

CREATE DEFINER='root'@'localhost' FUNCTION 'existe_EMAIL'(pEMAIL VARCHAR(50)) RETURNS BOOLEAN
BEGIN

    DECLARE existe boolean DEFAULT FALSE;

    IF EXISTS (SELECT email FROM tb_cliente where email = pEMAIL) THEN
        SET existe = TRUE;
    END IF;

RETURN existe;

END//
DELIMITER ;