Mysql – How to select return value from thesql prepared statement

databaseMySQL

I am writing a stored procedure in MySQL. The following is the code I wrote:

SET @qry = 'SELECT id into @wid FROM work_profile where candidate_id = 1223 limit 1';
PREPARE statement FROM @qry;
EXECUTE statement;

However, @wid is null after execution.

SELECT id FROM work_profile where candidate_id = 1223 limit 1

This returns a value of 1443.

Best Answer

The following works for me:

USE test;
DELIMITER $$
CREATE PROCEDURE GETNID()
BEGIN
  SET @query = 'SELECT 100 INTO @nid';
  PREPARE statement1 FROM @query;
  EXECUTE statement1;
END$$
DELIMITER ;

And to call the procedure:

CALL GETNUMBER();
SELECT @nid;

However, this does not seem to work in the MySQL Query Browser. I had to resort to using the MySQL command line client. I suspect the Query Browser clears the session after each statement.

For more EXECUTE examples, see the MySQL manual:

Related Topic