MySQL: null check based on if statement

Question

I have this procedure:

DELIMITER //
CREATE PROCEDURE add_package(nam VARCHAR(255), pack_id INT, lib_id INT, descrip TEXT)
  BEGIN
  DECLARE ind INT;
  DECLARE `_rollback` BOOL DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    START TRANSACTION;
      INSERT INTO java_master (name, description) VALUES (nam, descrip);

      SET ind = (SELECT id FROM java_master WHERE name = nam AND description = descrip);

      INSERT INTO java_package (master_id, parent_package_id, library_id) VALUES (ind, pack_id, lib_id);
      IF `_rollback` THEN
        ROLLBACK;
      ELSE
        COMMIT;
    END IF;
  END //
DELIMITER ;

I need to make that set statement in the middle get both return the id in the java_master table even if description is a null value... any suggestions as to how I can do this?

Voidmain · Accepted Answer · 2015-02-17 22:18:30Z

1

Since you are not supplying a value for the "id" column, I'm going to assume your id is an auto incrementing value.

-- What I imagine your table looks like? SHOW CREATE TABLE java_master;

CREATE TABLE java_master (id INTEGER PRIMARY KEY AUTO_INCREMENT, name CHAR(10), description VARCHAR(200));

-- Example inserting null

INSERT INTO java_master(name, description)
VALUES("class", NULL);

-- If this is the case, you should be using LAST_INSERT_ID() to get the last created auto incrementing value for the table. SET ind to this.

SELECT LAST_INSERT_ID();

If you insist on doing it how you have it, then I supposed you could do this:

SELECT id FROM java_master WHERE name = nam AND IFNULL(description, "") = IFNULL(descrip, "");

http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id

edited Feb 17, 2015 at 22:18

answered Feb 17, 2015 at 22:13

Voidmain

1413 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Totalllyrandomguy Over a year ago

I do not at all insist on doing it my way... LAST_INSERT_ID() is SOOO much easier and more fool_proof! thanks so much!

Totalllyrandomguy · Accepted Answer · 2015-02-17 22:46:16Z

0

Using the answer provided by Voidmain, the solution to my problem is made irrelevant with the following code...

DELIMITER //
CREATE PROCEDURE add_package(nam VARCHAR(255), pack_id INT, lib_id INT, descrip TEXT)
  BEGIN
  DECLARE `_rollback` BOOL DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    START TRANSACTION;
      INSERT INTO java_master (name, description) VALUES (nam, descrip);
      INSERT INTO java_package (master_id, parent_package_id, library_id) VALUES ((SELECT LAST_INSERT_ID()), pack_id, lib_id);
      IF `_rollback` THEN
        ROLLBACK;
      ELSE
        COMMIT;
    END IF;
  END //
DELIMITER ;

answered Feb 17, 2015 at 22:46

Totalllyrandomguy

1492 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL: null check based on if statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related