title: MySQL stored function to read column from row in table
body: <p>We need a function / stored procedure to return a column in a row in a table, where we pass the table, id field name, id and column to return.</p>
  
  <p>We have a table which is an activity log with columns such as </p>
  
  <ul>
  <li><p>log_datetime  - the date/time the user did something</p></li>
  <li><p>log_table   - the table affected</p></li>
  <li><p>log_idcol   - the column in log_table that holds the id</p></li>
  <li><p>log_id      - the id of the row in log_table</p></li>
  <li><p>log_titlecol - the column in log_table which holds the 'title' of the
  item</p></li>
  </ul>
  
  <p>So in a report/ MySql statement I need to be able to list the title which is held in the column name held in log_titlecol,  read from the table with the name in the log_table column, and id of whatever is in log_idcol.</p>
  
  <p>so like </p>
  
  <p>SELECT log_titlecol FROM log_table where log_idcol = log_id </p>
  
  <p>where all the 'log_; parts are replaceable.  I can't see a way to do this in MySQL, so is there a way to do this in a stored procedure/function?</p>
  
  <p>For example:</p>
  
  <pre><code>CREATE TABLE jokes (
    JokeID smallint(4) NOT NULL, **(autoincrement, unique)** 
    Title varchar(50) CHARACTER SET utf8 DEFAULT NULL,
    Note text CHARACTER SET utf8
  ) 
  
  CREATE TABLE idioms (
    IdiomID smallint(4) NOT NULL,  **(autoincrement, unique)**
    IdiomTitle varchar(50) CHARACTER SET utf8 DEFAULT NULL,
    Note text CHARACTER SET utf8
  ) 
  
  CREATE TABLE `log` (
    id smallint(4) NOT NULL, **(autoincrement, unique)**
    log_datetime datetime,
    log_table varchar(50) CHARACTER SET utf8 DEFAULT NULL,
    log_idcol varchar(50) CHARACTER SET utf8 DEFAULT NULL,
    log_id smallint(4) NOT NULL,
    log_titlecol varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  ) 
  
  
  INSERT INTO jokes (Title, Notes) 
  VALUES ('Funny joke','This is note1')
  
  INSERT INTO jokes (Title, Notes) 
  VALUES ('Another Funny joke','This is another note')
  
  INSERT INTO idioms (IdiomTitle, Notes) 
  VALUES ('Bird in the hand','What this means..')
  
  INSERT INTO jokes (Title, Notes) 
  VALUES ('Another Funny joke','This is another note')
  
  INSERT INTO log (log_datetime, log_table, log_idcol, log_id , log_titlecol) 
  VALUES (now(), 'jokes','JokeID',1,'Title')
  
  INSERT INTO log (log_datetime, log_table, log_idcol, log_id , log_titlecol) 
  VALUES (now(), 'jokes','JokeID',2,'Title')
  
  INSERT INTO log (log_datetime, log_table, log_idcol, log_id , log_titlecol) 
  VALUES (now(), 'idioms','IdiomID',2,'IdiomTitle')
  </code></pre>
  
  <p>So now how can I have a report from log showing the date time and the Title column from the Jokes table, row id as in the log_id column? I need a function.</p>
  
  <p>eg  SELECT log_datetime, log_table, log_id, GetTitle(log_table, log_idcol, log_id, log_titlecol) from log</p>
  
  <p>Where GetTitle is a function which will return the column held in 'log_titlecol' form the table passed as log_table, from the row with an id (held in the log_idcol column) of log_id</p>
  
  <p>So for example the output would show:</p>
  
  <p><code>2018-01-01 12:00    jokes   1   Funny Joke 
   2018-01-01 12:10    jokes   2   Another Funny joke
   2018-01-01 12:11    idioms  1   Bird in the hand</code></p>
  
  <p>I have tried</p>
  
  <pre><code> CREATE PROCEDURE Getcol(IN tab TEXT CHARSET utf8mb4, IN col TEXT CHARSET utf8mb4, IN idcol TEXT CHARSET utf8mb4, IN id INT(15), OUT outcol TEXT CHARSET utf8mb4)
   DETERMINISTIC
   COMMENT 'Return a column from any table'
   BEGIN
       SET @Expression = CONCAT('SELECT ', col,' INTO @outcol FROM ', tab, ' where ', idcol, ' = ', id); 
       PREPARE myquery FROM @Expression; 
       EXECUTE myquery; 
       SELECT @outcol;
   END
  </code></pre>
  
  <p>I can call this like
      <code>CALL GetCol('Jokes','Title','JokeID',1)</code></p>
  
  <p>And this works to return the Title column for ID 1, but I am cant seem to then put this call into a function</p>
  
  <pre><code> CREATE FUNCTION getrowcol(tab TEXT, col TEXT, idcol TEXT, id INT) RETURNS text CHARSET utf8mb4
       NO SQL
     COMMENT 'Return a column from any table'
     BEGIN
          DECLARE outvar TEXT;
          CALL GetCol(tab, col, idcol, id, @out1);
          SELECT @out1 INTO outvar;
          RETURN outvar;
     END
  </code></pre>
  
  <p>This returns blank. How can I return @out1?</p>
  
  <p>This is essentially the same question (with no answer)</p>
  
  <p><a href="https://dba.stackexchange.com/questions/151328/dynamic-sql-stored-procedure-called-by-a-function">https://dba.stackexchange.com/questions/151328/dynamic-sql-stored-procedure-called-by-a-function</a></p>