Re: Error in IF block

[William Chiquito]

Hi Anjanesh,

My test works without problem:

CREATE TABLE `tbl1` (
  `id` int(11) NOT NULL auto_increment,
  `uniqueKEY` int(11) default NULL,
  `name` varchar(50) collate latin1_spanish_ci default NULL,
  `age` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

DELIMITER $$

DROP PROCEDURE IF EXISTS `mysp`$$

CREATE PROCEDURE `mysp`()
BEGIN
	SET @c := (SELECT `id` FROM `tbl1` WHERE `uniqueKEY` = '12345');
	IF @c IS NULL THEN
		INSERT INTO `tbl1` VALUES ('', '12345', 'My Name', '26');
		SET @c := LAST_INSERT_ID();
	END IF;
END$$

DELIMITER ;

CALL mysp();

SELECT * FROM tbl1;

Result:

    id  uniqueKEY  name      age  
------  ---------  -------  ------
     1      12345  My Name      26

SELECT @c;

Result:

@c    
------
1