title: How can I select out parameters in a MySQL procedure as table columns?
body: <p>Environment: MySQL 5.1, Linux</p>
  
  <p>I have a stored procedure that computes several values from a single input value. The values are returned as OUT parameters. I would like to call this procedure for every row set and have the values appear as columns in the result set. The values have a complex relationship such that distinct <em>functions</em> for each value is not easily constructed.</p>
  
  <p>The question: How can I get OUT parameters to show up as columns in a table?</p>
  
  <p>Here's what I have so far:</p>
  
  <pre><code>DELIMITER $_$
  DROP PROCEDURE IF EXISTS in_out;
  CREATE PROCEDURE in_out (
         IN s TEXT, 
         OUT op TEXT, 
         OUT opn INT,
         OUT opd TEXT,
         OUT len INT
         )
  BEGIN
          SET op = 'del';
          SET opn = 1;
          SET opd = substr(s,4);
          SET len = LENGTH(SUBSTR(s,4));
  END
  $_$
  DELIMITER ;
  </code></pre>
  
  <p>Then:</p>
  
  <pre><code>mysql&gt; call in_out('delACT',@op,@opn,@opd,@len);
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; select @op,@opn,@opd,@len;
  +------+------+------+------+
  | @op  | @opn | @opd | @len |
  +------+------+------+------+
  | snv  |    1 | ACT  |    3 |
  +------+------+------+------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>So far so good, but I can't figure out how to call this procedure for every row and return the results in the result set.  I want is something like this:</p>
  
  <pre><code>dream&gt; select mycol,in_out(mycol) from mytable
  +---------+------+------+------+------+
  | mycol   | @op  | @opn | @opd | @len |
  +---------+------+------+------+------+
  | delACT  | del  |    1 | ACT  |    3 |
  +---------+------+------+------+------+
  </code></pre>
  
  <p>Thanks!</p>