Hi Anjanesh,
A option is:
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 c := LAST_INSERT_ID();
END IF;
END$$
DELIMITER ;
/*
IMPORTANT: Not to call to the parameters of stored procedure just as the columns of the tables.
*/
CALL mysp(12345, 'John', 26, @d);
SELECT * FROM tbl1;
Result:
id uniqueKEY name age
------ --------- ------- ------
1 12345 John 26
SELECT @d;
Result:
@d
------
1
Another option is:
DELIMITER $$
DROP PROCEDURE IF EXISTS `mysp`$$
CREATE PROCEDURE `mysp`(IN puniqueKEY INT, IN pName VARCHAR(50), IN pAge INT)
BEGIN
SET @c := (SELECT `id` FROM `tbl1` WHERE `uniqueKEY` = puniqueKEY);
IF @c IS NULL THEN
INSERT INTO `tbl1` VALUES ('', puniqueKEY, pName, pAge);
SET @c := LAST_INSERT_ID();
END IF;
END$$
DELIMITER ;
CALL mysp(12345, 'John', 26);
SELECT * FROM tbl1;
Result:
id uniqueKEY name age
------ --------- ------- ------
1 12345 John 26
SELECT @c;
Result:
@c
------
1
See
9.4. User-Defined Variables