MySQL INSERT WHERE a variable is not duplicated

Question

I have some troubles with DECLARE, CALL function and a IF settelment. What i have so far is:

CREATE PROCEDURE number_of_projects(project_name VARCHAR)
BEGIN
    DECLARE variable INT;
    SET variable = 4;
    SELECT variable;

    SELECT count(project_id) FROM atm_projects WHERE project_name = variable;
END

IF number_of_projects("PROJECT NAME") = 0
    THEN
    INSERT INTO atm_projects(project_id,project_name,added_from_mti)
    VALUES (project_id,'PROJECT NAME',1)
ENDIF

The main goal is to insert a row into a table where project_name is not duplicated.

I could change project_name to an UNIQUE key but please tell me what is wrong with my code, an how can i fix this?

I need to learn how a PROCEDURE, CALL procedure, IF works.

You can't use an `if` statement outside of a function, trigger or procedure. and you are missing a delimiter around your procedure. — juergen d, Jul 26 '12 at 11:05

score 1 · Answer 1 · answered Jul 26 '12 at 11:03

1

try:

CREATE PROCEDURE number_of_projects(project_name VARCHAR(255))
BEGIN
    DECLARE var_project_no INT;
    SET var_project_no = 0;
    SELECT var_project_no;

    SELECT count(project_id) INTO var_project_no FROM atm_projects WHERE var_project_name = 4;


    IF var_project_no = 0
    THEN
        INSERT INTO atm_projects(project_id,project_name,added_from_mti)
        VALUES (project_id,'PROJECT NAME',1)
    END IF;
END;

answered Jul 26 '12 at 11:03

Omesh

27,801
6
42
51

i just need to check if project_name is not duplicate like "PROJECT1" == "PROJECT1", and another thing, PROCEDURE is like a function, method in PHP? – Empeus Jul 26 '12 at 11:27
and one more thing how do i put this **SELECT count(project_id) INTO var_project_no FROM atm_projects WHERE project_name = 'PROJECT NAME';** into a variable? – Empeus Jul 26 '12 at 11:29
using INTO clause you can do that. no its somewhat different from php functions, stored procedure(sp) is basically a set of queries and if else conditions. you can create functions in mysql which can return values but sp cant. – Omesh Jul 26 '12 at 11:36
do you have a little bit of spare time to see how i did accomplished this task in PHP? http://codepad.org/oIMOb5nA – Empeus Jul 26 '12 at 12:00
ya sure.. if you have a unique key on column type_of_work_name then you dont need to use another query for select, you can directly use INSERT INTO table ON DUPLICATE KEY UPDATE.. – Omesh Jul 26 '12 at 12:49
and i observed that **INSERT INTO table ON DUPLICATE KEY UPDATE** increments the auto increment number, so i my database i have something like 1 2 3 7576 7755 5774 ... i don't want to upldate the auto increment – Empeus Jul 27 '12 at 10:36
that's probably a bug in older version of MySQL with InnoDB. Alternate you can try NOT EXISTS see: http://stackoverflow.com/questions/9189102/too-many-auto-increments-with-on-duplicate-key-update – Omesh Jul 30 '12 at 04:27

score 0 · Answer 2 · answered Jul 26 '12 at 10:57

0

You have declared project_name to varchar without specifying the length. Change it to varchar(100)

answered Jul 26 '12 at 10:57

Madhivanan

13,470
1
24
29

score 0 · Answer 3 · answered Jul 26 '12 at 11:09

CREATE PROCEDURE number_of_projects(project_name VARCHAR(255))
BEGIN

 if NOT  exists(SELECT Top 1 1  FROM atm_projects WHERE var_project_name = 4)
 BEGIN 
   INSERT INTO atm_projects(project_id,project_name,added_from_mti)
   Select project_id,project_name ,1
 END
END;

MySQL INSERT WHERE a variable is not duplicated

3 Answers3