CODEWITHSUNDEEP
mysqlstored-proceduresreturncommand-line-interfacelastinsertid
Mallow
Mallow

Reputation: 864

How to OUT from a stored procedure using LAST_INSERT_ID()

Mysql Version 14.14 Distrib 5.1.41

I have a stored procedure that I am trying to get compiled but an error keeps cropping up. My stored procedure prepares an insert statement, executes it, deallocates the prepared statment and then attempts to return the last insert id. This is an pseudo extract:

1 CREATE PROCEDURE `audit`( IN pExampleValue varchar(50))
2 
3 BEGIN
4 PREPARE stmt FROM 'INSERT INTO `Audit` (`Value`) VALUES (?)';
5 EXECUTE stmt USING @pExampleValue;
6 DEALLOCATE PREPARE stmt
7 
8 RETURN LAST_INSERT_ID() AS `AuditId`;
9 END$$

From what I am able to gather my version of mysql can used prepared statements in stored procedures. However the error I am getting is. 

SQL Error (1313): RETURN is only allowed in a FUNCTION

As the error says I cannot return from a procedure. However I can out from a procedure? I'm having a difficult time finding examples of a query being OUT'ed, How exactly would I do that from this example.

Upvotes: 0

Views: 3348

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220932

Either, declare an OUT parameter, or make your procedure a FUNCTION.

You'll find sufficient documentation here:

http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html

Upvotes: 2

Related Questions