Re: Error in IF block

[Anjanesh Lekshminarayanan]

Ah..Thanks William. The MySQL Docs on Stored Procedures is too little.

DROP TABLE IF EXISTS `tbl1`;
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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

DELIMITER $$

DROP PROCEDURE IF EXISTS `mysp`$$
CREATE PROCEDURE `mysp`(IN puniqueKEY INT, IN pName VARCHAR(50), IN pAge INT, OUT c INT)
BEGIN
        DECLARE a INT;
	SET a := (SELECT `id` FROM `tbl1` WHERE `uniqueKEY` = puniqueKEY);
	IF a IS NULL THEN
		INSERT INTO `tbl1` VALUES ('', puniqueKEY, pName, pAge);
		SET a := LAST_INSERT_ID();
	END IF;
	SELECT a INTO c;
END$$

DELIMITER ;

CALL mysp(12345, 'John', 26, @d);
SELECT @d;

Works !
Thanks Again.

Anjanesh