DELIMITER $$

DROP PROCEDURE IF EXISTS mylibrary.categories_insert$$

CREATE PROCEDURE `mylibrary`.`categories_insert`(IN newcatname VARCHAR(60), IN parent INT, OUT newid INT)
proc: BEGIN
  DECLARE cnt INT;
  SET newid=-1;

  -- basic validation
  SELECT COUNT(*) FROM categories 
  WHERE parentCatID=parent INTO cnt;
  IF ISNULL(newcatname) OR TRIM(newcatname)="" OR cnt=0 THEN
    LEAVE proc;
  END IF;

  -- test if category already exists
  SELECT COUNT(*) FROM categories 
  WHERE parentCatID=parent AND catName=newcatname 
  INTO cnt;
  IF cnt=1 THEN 
    SELECT catID FROM categories 
    WHERE parentCatID=parent AND catName=newcatname 
    INTO newid;
    LEAVE proc; 
  END IF;

  -- actually insert new category
  INSERT INTO categories (catName, parentCatID)
  VALUES (newcatname, parent);
  SET newid = LAST_INSERT_ID();
END proc$$

DELIMITER ;

# NOTE:
# If you have an error on character sets, change
# the character set and collation of the table as follows:
#   ALTER TABLE categories CHARACTER SET latin1 
#       COLLATE latin1_swedish_ci;
######################################################

