How to select return value from mysql prepared statement?

Question

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.

Answer 1

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:

• http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html