Failed to pass parameter in Mysql procedure with subquery

Question

I am trying to create what I thought was a fairly simple procedure calling parameters for a column name.

This is the procedure I created:

    CREATE DEFINER=`root`@`%` PROCEDURE `test`(in col varchar(225))
BEGIN
-- Column of Averages
DROP temporary TABLE IF EXISTS temp; 

CREATE temporary TABLE IF NOT EXISTS temp AS 
  SELECT col, 
         Round(Avg(bcount), 0) AS `Average # of Loans` 
  FROM   (SELECT col, 
                 Count(borrower) AS bcount 
          FROM   whatif_dataset 
          WHERE  loan_lienposition = 'first' 
          GROUP  BY col, 
                    Month(Str_to_date(status_fundingdate, '%m/%d/%Y')), 
                    Year(Str_to_date(status_fundingdate, '%m/%d/%Y')))AS dt 
  GROUP  BY col; 

END

However, when I call the procedure, I get only one ie entry:

Col                Average # of Loans
'Passed Parameter'   757

It seems to me that the parameter does not pass correctly. Are there any limitations in MYSQL that prevent me from doing this?

When I run the same query with the specific column name currentprocessor, this is the desired result:

    currentprocessor    Average # of Loans
    proc1               20
    proc2               12
    proc3               8
    proc4               22
    proc5               24

Answer 1

I managed to get this fix using a prepared statement

 CREATE DEFINER=`root`@`%` PROCEDURE `test`(in col varchar(225))
BEGIN
-- Column of Averages
DROP temporary TABLE IF EXISTS temp; 


SET @sql= CONCAT('SELECT ', col,',', 
         'Round(Avg(bcount), 0) AS `Average # of Loans` 
  FROM   (SELECT ', col,', 
                 Count(borrower) AS bcount 
          FROM  whatif_dataset 
          WHERE  loan_lienposition = ''first'' 
          GROUP  BY ', col,', 
                    Month(Str_to_date(status_fundingdate, "%m/%d/%Y")), 
                    Year(Str_to_date(status_fundingdate, "%m/%d/%Y")))AS dt 
  GROUP  BY ', col); 

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END