title: MySQL calling a procedure with select statements from a trigger - "Not allowed to return a result set from a trigger"
body: <p>Here's one for the SQL wizards.  Similar questions have been asked before and I have some idea what the problem is but I'm having trouble rewriting the select statements inside my procedure to avoid returning a result set, and returning the global vars my trigger wants. </p>
  
  <p>For a bit of background, what I'm trying to accomplish here is to use a group of triggers to call procedures which will calculate the percentile of a value relative to other rows in the table with the same gene_id, on insert or update of rows.</p>
  
  <p>(using PMA delimiter is set to |)</p>
  
  <p>The procedure to calculate the percentile value works fine</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS gecg_relative_expr_compendium |
  
  CREATE PROCEDURE gecg_relative_expr_compendium( IN in_gene_id varchar(24), IN in_fpkm double, OUT percentile float(4,2) ) 
  BEGIN 
      SET @row := 0;
      SELECT fpkm, ( (rank / @row) *100 ) AS percentile
      FROM (
          SELECT fpkm, @row := @row +1 AS rank, gene_id
          FROM gene_expression_cufflinks_gene
          WHERE gene_id = in_gene_id
          ORDER BY fpkm ASC
      ) AS p
      WHERE fpkm = in_fpkm;
  END |
  </code></pre>
  
  <p>I can call it like so:</p>
  
  <pre><code>CALL gecg_relative_expr_compendium('ENSG00000251948', 19367800, @percentile)
  </code></pre>
  
  <p>Returns:</p>
  
  <pre><code>fpkm    percentile
  19367800    100.0000
  </code></pre>
  
  <p>And my trigger:</p>
  
  <pre><code>DROP TRIGGER IF EXISTS insert_gecg_relative_expr |
  
  CREATE TRIGGER insert_gecg_relative_expr
  
   BEFORE INSERT ON `gene_expression_cufflinks_gene` FOR EACH ROW  
   BEGIN  
      CALL gecg_relative_expr_compendium(NEW.gene_id, NEW.fpkm, NEW.fpkm_percentile_compendium) ;
   END  |
  </code></pre>
  
  <p>When I insert a row triggering the trigger, it fails (irrelevant columns trimmed for clarity)</p>
  
  <pre><code> INSERT INTO `mctp`.`gene_expression_cufflinks_gene` (
  `gene_id` ,
  `fpkm`,
  `fpkm_percentile_compendium` ,
  `fpkm_percentile_origin_tissue` ,
  `fpkm_percentile_collection_tissue` ,
  `fpkm_percentile_sample_cancer`
  )
  VALUES ('ENSG00000239906','555', NULL , NULL , NULL , NULL , NULL , NULL)
  </code></pre>
  
  <p>Eventually I want my trigger to insert the calculated NEW percentile values into the row being inserted, such as NEW.fkpm_percentile_compendium. Precalculating these percentile values is going to be necessary for performance reasons because this table will be > 500m rows, and eventually other percentile values will have to be computed based on relations, resulting in excessive joins.</p>
  
  <p>I have a vague idea that the solution should involve SELECT INTO variables instead of a plain select statement, but this breaks my procedure.  </p>
  
  <pre><code>DROP PROCEDURE IF EXISTS gecg_relative_expr_compendium |
  
  CREATE PROCEDURE gecg_relative_expr_compendium( IN in_gene_id varchar(24), IN in_fpkm double, OUT percentile float(4,2) ) 
  BEGIN 
      SET @row := 0;
      SELECT fpkm, ( (rank / @row) *100 ) AS percentile INTO fpkm, percentile
      FROM (
          SELECT fpkm, @row := @row +1 AS rank, gene_id
          FROM gene_expression_cufflinks_gene
          WHERE gene_id = in_gene_id
          ORDER BY fpkm ASC
      ) AS p
      WHERE fpkm = in_fpkm;
  END |
  </code></pre>
  
  <p>gives me:</p>
  
  <pre><code>#1327 - Undeclared variable: fpkm 
  </code></pre>
  
  <p>So my questions are</p>
  
  <p>1> How can I access the new vars I've selected INTO from within the same procedure? </p>
  
  <p>2> how can I do this in such a way as to return the vars to the trigger without returning a result set? </p>
  
  <p>Thanks so much for your help.  </p>
  
  <hr>
  
  <p>UPDATE >>  Here are the working procedure and trigger:</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS gecg_relative_expr_compendium |
  CREATE PROCEDURE gecg_relative_expr_compendium( IN in_gene_id varchar(24), IN in_fpkm double, OUT out_fpkm double, OUT out_percentile float(5,2)  ) 
  BEGIN 
      SET @row := 0;
      SELECT fpkm, ( (rank / @row) *100 ) AS percentile
      FROM (
          SELECT fpkm, @row := @row +1 AS rank, gene_id
          FROM gene_expression_cufflinks_gene
          WHERE gene_id = in_gene_id
          ORDER BY fpkm ASC
      ) AS p
      WHERE fpkm = in_fpkm
      LIMIT 1 
      INTO out_fpkm, out_percentile;
  END |
  
  
  
  DROP TRIGGER IF EXISTS insert_gecg_relative_expr |
  
  CREATE TRIGGER insert_gecg_relative_expr
  
   BEFORE INSERT ON `gene_expression_cufflinks_gene` FOR EACH ROW  
   BEGIN  
      CALL gecg_relative_expr_compendium(NEW.gene_id, NEW.fpkm, @f, @p);
      SET NEW.fpkm_percentile_compendium = @p;
   END  
  |
  </code></pre>
  
  <p>The trigger runs the percentile calculation on a row as it gets inserted. Now on to the next task, to run that procedure on all the other rows that match this gene_id. guhhh.</p>
  
  <p>xoxoxox </p>