Procedure with IF NOT EXISTS

Question

Procedure with IF NOT EXISTS

Navigation

#1 by (5 votes)

3

Hello stackoverflow developers, I started to delve into sql commands a short time ago, I'm having some kind of syntax error with this procedure, but for a number of attempts, I could not fix it, follow the code there, I'd be very grateful if can you help me?

DELIMITER $$

CREATE procedure Inserir_Disciplina(@disciplina_nome VARCHAR(45) CHARSET UTF8)
BEGIN
IF NOT EXISTS (SELECT * FROM tcc.disciplina WHERE disciplina_nome = @disciplina_nome)
THEN
    INSERT INTO tcc.disciplina(disciplina_nome) 
     VALUES(@disciplina_nome); 
END IF
END$$
DELIMITER ;

Optionally what would this procedure look like with an else?

After Attempts and Errors ...

I edited the procedure to the following way, as your friend Christian Passold recommended, get the code half mouth, if you help someone:

DELIMITER $$

CREATE PROCEDURE Inserir_Disciplina(IN p_disciplina_nome VARCHAR(45) CHARSET UTF8)
BEGIN
DECLARE numero_de_rows INT DEFAULT (SELECT count(*) FROM tcc.disciplina WHERE disciplina_nome = @disciplina_nome);

  IF(numero_de_rows = 0) THEN
        INSERT INTO tcc.disciplina(disciplina_nome) 
         VALUES(p_disciplina_nome);
    SELECT "Disciplina inserida com sucesso";
    ELSE 
      SELECT "Erro, disciplina ja existente no banco de dados!";
    END IF;

END$$
DELIMITER ;

mysql sql procedure

asked by anonymous 29.09.2014 / 01:10

1 answer

How to use the pointer concept in php? Reload page with MVC

score 5 · Accepted Answer

DELIMITER $$

Delete the procedure if it exists, only for security, after creating it again.

DROP PROCEDURE IF EXISTS Inserir_Disciplina $


CREATE PROCEDURE Inserir_Disciplina(p_disciplina_nome VARCHAR(45))
BEGIN

You can create a variable containing the amount of select records

    DECLARE v_select INT DEFAULT (SELECT count(*) FROM tcc.disciplina WHERE disciplina_nome = p_disciplina_nome);

Thus, it checks if it is greater than 0, if it is, it inserts.

    IF(v_select > 0) THEN
        INSERT INTO tcc.disciplina(disciplina_nome) 
         VALUES(p_disciplina_nome);
    ELSE 
       #Do something here
    END IF;

END$$
DELIMITER ;

I hope I have helped. : D