CODEWITHSUNDEEP
mysqlfunctionmysql-error-1064
Yuri Oliveira
Yuri Oliveira

Reputation: 21

CREATE FUNCTION ERROR IN MYSQL

I'm having this error: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 19

DELIMITER $$
CREATE FUNCTION cadastrar_usuario(email VARCHAR(100), senha VARCHAR(30), nome VARCHAR(100), sobrenome VARCHAR(100)) RETURNS INT
BEGIN
DECLARE ID INT;
DECLARE EXISTE_EMAIL INT;

IF (email = '') || (senha = '') || (nome = '') || (sobrenome = '') THEN
    RETURN 1;

SELECT LAST_INSERT_ID() INTO ID;

SELECT COUNT(CODUSUARIO) FROM USUARIO_EMAIL WHERE EMAIL = email INTO EXISTE_EMAIL;
IF EXISTE_EMAIL > 0 THEN
    RETURN 2;

INSERT INTO USUARIO VALUES(0, senha, nome, sobrenome);
INSERT INTO USUARIO_EMAIL VALUES(ID, email, NULL);

RETURN 0;
  **END$$** This is the line 19

    DELIMITER ;

I Have no idea, what is the problem.

Thanks already.

Upvotes: 0

Views: 82

Answers (1)

LSerni
LSerni

Reputation: 57453

You forgot to close your IF blocks:

DROP FUNCTION IF EXISTS cadastrar_usuario;

DELIMITER $$
CREATE FUNCTION cadastrar_usuario(email VARCHAR(100), senha VARCHAR(30), 
    nome VARCHAR(100), sobrenome VARCHAR(100)) RETURNS INT
BEGIN
DECLARE ID INT;
DECLARE EXISTE_EMAIL INT;

IF (email = '') || (senha = '') || (nome = '') || (sobrenome = '') THEN
    RETURN 1;
END IF;

SELECT LAST_INSERT_ID() INTO ID;

SELECT COUNT(CODUSUARIO) FROM USUARIO_EMAIL WHERE EMAIL = email INTO EXISTE_EMAIL;

IF EXISTE_EMAIL > 0 THEN
    RETURN 2;
END IF;

INSERT INTO USUARIO VALUES(0, senha, nome, sobrenome);
INSERT INTO USUARIO_EMAIL VALUES(ID, email, NULL);

RETURN 0;

END

$$

DELIMITER ;

( Tested and working on MySQL 5.6.12-community ).

Upvotes: 1

Related Questions