current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I am using below sample stored procedure in my application:

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_item`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_item`(
    IN name VARCHAR(255),
    OUT item_id INT(11)
)
BEGIN   
    DECLARE item_id INT DEFAULT 0;
    START TRANSACTION;
    INSERT INTO `item` (`name`) VALUES(name);   
    SET @item_id := LAST_INSERT_ID();
    COMMIT;
END$$
DELIMITER ;

When i execute this procedure using:-

CALL sp_item("TEST1",@item_id);

and fetch the last inserted id using:-

SELECT @item_id;

Then i get NULL as a result however the records is inserted successfully into database.

I could not find any relevant help after Googling.

What i am missing here?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

You are not using the out parameter. Instead, you're feeding a local variable with a similar name. Try this:

CREATE PROCEDURE `sp_item`(
    IN name VARCHAR(255),
    OUT item_id INT(11)
)
BEGIN   
    START TRANSACTION;
    INSERT INTO `item` (`name`) VALUES(name);   
    SET item_id := LAST_INSERT_ID();
    COMMIT;
END$$
share|improve this answer
    
Thanks for putting me on the right track. –  neeraj Jan 4 '13 at 11:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.