Stonesmit Stonesmit - 5 months ago 10
SQL Question

MySql stored procedure returns value error

I am using the following stored procedure:

DELIMITER $$
USE `customer`$$
DROP PROCEDURE IF EXISTS `InsertCustomerEmail`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertCustomerEmail`(IN p_Customer_ID INT(11),
IN p_from_who VARCHAR(50),
IN p_to_whom VARCHAR(50),
IN p_CC VARCHAR(50),
IN p_BCC VARCHAR(50),
IN p_Subject VARCHAR(500),
IN p_Massage VARCHAR(4000),
IN p_Is_Sent BIT(1),
IN p_Sent_When DATE,
IN p_Is_Active BIT(1),
OUT new_ID INT)
BEGIN
INSERT INTO customer_emails (
`Customer_ID`,
`from_who`,
`to_whom`,
`CC`,
`BCC`,
`Subject`,
`Massage`,
`Is_Sent`,
`Sent_When`,
`Is_Active`
) VALUES (p_Customer_ID,
p_from_who,
p_to_whom,
p_CC,
p_BCC,
p_Subject,
p_Massage,
p_Is_Sent,
p_Sent_When,
p_Is_Active);
SET @new_ID=SCOPE_IDENTITY();
END$$
DELIMITER ;


My database has an auto increment ID column, I would like to return this
ID
(the last one added) in the
New_ID
variable but when I run the CALL for the procedure it returns
NULL
for the
New_ID
.

Any suggestions?

Thanks!

Answer

The reason being the @new_ID assignment is not considered part of the transaction; the INSERT statement is. Move the following line outside the BEGIN ... END

SET @new_ID=SCOPE_IDENTITY(); 

Since you have only one operation in the batch, you can loose the BEGIN...END

EDIT

As Thorsten Dittmar suggested I need to explain a bit further. The @new_ID assignment is executed right away, since it is not considered DML action and is not part of the batch. Thus SCOPE_IDENTITTY() is executed, but there is no identity generated yet, because the INSERT statement IS considered part of the batch and it is executed at its end.

EDIT 2

Since the OP added MySQL tag, above statement won't work, since there is no SCOPE_IDENTITY() in MySQL. Instead the correct function is LAST_INSERT_ID().

SET @new_ID=LAST_INSERT_ID();