Mysql - Stored procedure OUT variable return null

Question

My Table Structure is:

DROP TABLE IF EXISTS `child`;

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `map_parent_child`;

CREATE TABLE `map_parent_child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `child_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_parent_child` (`parent_id`,`child_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `parent`;

CREATE TABLE `parent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have create a stored procedure like

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_parent`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_parent`(
    IN parent_name VARCHAR(255),
    IN child_name VARCHAR(255),
    OUT parent_id INT(11))
BEGIN   
    DECLARE parent_id INT DEFAULT 0;
    DECLARE child_id INT DEFAULT 0;

    START TRANSACTION;

        INSERT INTO `parent` (`name`) VALUES(parent_name);      

        SET parent_id = LAST_INSERT_ID();

        INSERT INTO `child` (`name`) VALUES(child_name);

        SET child_id = LAST_INSERT_ID();

        INSERT INTO `map_parent_child` (`parent_id`,`child_id`) VALUES(parent_id,child_id); 
    commit;
END$$
DELIMITER ;

CALL sp_parent("test", "test", @parentid);

But when i try to fetch output variable using select then i get NULL however all INSERT statement work fine and adding record into the database table.

SELECT @parentid;

What i am missing here?

Due to words limitation i can not paste sql query for the structure of those tables — Neeraj
– Neeraj, Commented Dec 12, 2012 at 6:26
why I am asking is..? that parent table must not have any other fields as not null in order to ** insert query **success. Just check whether that insert query being inserted successfully. If it is so, the last_record will store that value. Keep one other thing mind, that It will not store the value which is explicitely inserted — Chella
– Chella, Commented Dec 12, 2012 at 6:38
There are just two fields(id and name) for parent and child table in which id is auto-incremented. — Neeraj
– Neeraj, Commented Dec 12, 2012 at 6:39

Paccc · Accepted Answer · 2013-03-19 01:03:41Z

8

You might have already solved this by now, but the first thing I noticed about your stored procedure is that you have a local variable with the same name as the output variable (parent_id). It looks to me that you're setting the value of the local variable rather than the return variable, so the caller never sees the correct value.

Perhaps removing the local parent_id variable declaration will solve your problem.

answered Mar 19, 2013 at 1:03

Paccc

1,21113 silver badges15 bronze badges

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

1 Comment

Mr_and_Mrs_D Over a year ago

I had this exact problem

Sashi Kant · Accepted Answer · 2012-12-12 06:29:59Z

1

The syntax for setting the variable is incorrect, use : like,

SET parent_id := LAST_INSERT_ID();
 SET child_id := LAST_INSERT_ID();

or You can do the setting as

select LAST_INSERT_ID() into parent_id;

edited Dec 12, 2012 at 6:29

answered Dec 12, 2012 at 6:12

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

3 Comments

Chella Over a year ago

you can use either way...! Its not a problem

Neeraj Over a year ago

Sashi: If i use SELECT LAST_INSERT_ID() into parent_id; then there is no use of OUT variable because you will simply get the expected output without using "OUT". But i want to use SET and return the auto-incremented value to the OUT variable so that if any other user see my stored procedure, could easily understand what it returns.... Make sense?

Neeraj Over a year ago

I have added the table structure in the question itself.

Collectives™ on Stack Overflow

Mysql - Stored procedure OUT variable return null

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related