title: How to pass a Dynamic column name to a function parameter in MySql?
body: <p>Okay, I'm only looking for a work around now. Could some one guide me?</p>
  
  <p><strong>EDIT:</strong><br>
  Okay, So apparantly there's no way to get a function to call a dynamic procedure in MySql.<br>
  But I really need to do this select statement dynamically!</p>
  
  <pre><code>SELECT Location FROM DemoTable WHERE No_of_Hospitals &gt; AvgCity(No_of_Hospitals)
  </code></pre>
  
  <p>Or find a better way to deal with this problem.
  The parameters in the AvgCity() will be dynamic all the time. That's kind of compulsory. The parameter passing will be the name of a column from the DemoTable.
  I need an efficient way to handle the Rules from the Rule Table and the DemoTable as I've stated below. 
  Its been 3 days now that i've been stuck on this.
  <strong>HELP!! :s</strong></p>
  
  <p>For reference, here's the old problem i had. And the flow of everything that i've got done so far-</p>
  
  <p><strong>Old Problem:</strong><br>
  Could some one help me out with a good way to go about this?
  I'm pretty new to MySql and I can't seem to find a simple answer for this anywhere.<br>
  I have two tables that I'm working with. The first table contains a lot of columns filled with Integer values such as:     </p>
  
  <pre><code> No_of_Hotels  No_of_Hospitals  
  </code></pre>
  
  <p>The second table is a Rule Table which contains a rule name followed by the actual rule.<br>
  Eg:</p>
  
  <p><code>ID    RuleName    Rule
    1    Example     No_of_Hospitals &gt; AvgCity(No_of_Hospitals)
  </code></p>
  
  <p>The function that I have right now is:</p>
  
  <pre><code> CREATE FUNCTION AvgCity(columnName text) RETURNS float(10)  
   DETERMINISTIC  
   BEGIN  
   DECLARE columnAvg float;  
   SELECT AVG(columnName)   
   INTO columnAvg 
   FROM DemoTable;
   RETURN (columnAvg);  
   END
  </code></pre>
  
  <p>But every time I pass a value to the function parameter - which is the name of one of the columns that I want the average for, I get a <code>0</code> in return.<br>
  I figured that this is happening because the parameter being passed into the <code>AVG()</code> Function is being passed as a string and not a column name.
  So that's <code>AVG("No_of_Hospitals")</code> instead of <code>AVG(No_of_Hospitals)</code> which also returns <code>0</code>.<br>
  How do I fix this and get a result?</p>
  
  <p>I'll be executing the whole thing after the result with:    </p>
  
  <pre><code>SELECT @Q:= CONCAT('SELECT Location FROM DemoTable WHERE ', Rules.Rule) FROM
  Rules     WHERE ID=1;
  PREPARE stq FROM @Q;
  EXECUTE stq;
  DEALLOCATE PREPARE stq;
  </code></pre>
  
  <p>It all goes in a SELECT statement so, I do need the <code>AvgCity()</code> to be a Function and NOT a Procedure.    </p>
  
  <p><strong>UPDATE 1:</strong><br>
  So I decided to put a procedure inside the function.<br>
  What i have now is this function:</p>
  
  <pre><code>CREATE FUNCTION AvgCity(colName text) RETURNS float(10)
  DETERMINISTIC
  BEGIN
  DECLARE colAvg float;
  CALL AvgCityProcedure(colName,colAvg);
  RETURN (colAvg);
  END    
  </code></pre>
  
  <p>And this Procedure:</p>
  
  <pre><code>CREATE PROCEDURE AvgCityProcedure(
  IN colName VARCHAR(100),
  OUT colAvg FLOAT)
  BEGIN
  SET @c1 = 0.0;
  SET @M:= colName;
  SET @QUERY:= CONCAT('SELECT AVG(',@M,') INTO @C1 FROM DemoTable');
  PREPARE stmt FROM @QUERY;
  EXECUTE stmt; 
  SET colAvg:=@C1;
  SELECT colAvg;
  END   
  </code></pre>
  
  <p><strong>UPDATE 2:</strong> <strong><em>I got the procedure working!</em></strong><br>
  Followed this really nice example:<br>
  <a href="https://stackoverflow.com/questions/5591338/my-sql-dynamic-query-execute-and-get-ouput-into-a-variable-in-stored-procedure">My SQL Dynamic query execute and get ouput into a variable in stored procedure</a><br>
  <s>But i can't seem to manage to insert any value inside <code>colAvg</code>.
  I tried a lot of combinations with the <code>@QUERY</code>, but i can't find the right one.
  I keep ending up with <code>ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1</code>    </p>
  
  <p>But if i remove the <code>INTO colAvg</code> part and just do 
  <code>SELECT @QUERY:= CONCAT('SELECT AVG(',@M,') FROM DemoTable');</code><br>
  It totally prints the answer to screen. I need to return that to <code>colAvg</code>.
  What am i doing wrong? </s><br>
  Alright! I got the Procedure working properly!
  I got another problem now.
  I get an error while returning values from my function to the procedure.<br>
  It says dynamic sql not allowed inside the function.<br>
  But i used the dynamic sql inside my procedure :S<br>
  Whats happening here?!<br>
  What I need it to look like in the end is:<br>
  <code>SELECT Location FROM DemoTable WHERE No_of_Hospitals &gt; AvgCity(No_of_Hospitals)</code>    </p>
  
  <p>Could someone please set up a small bounty on this? I haven't got a single answer in more than a week now. :(</p>