MySQL Input Parameters Add Flexibility to Crosstab Stored Procedures - Page 2

April 7, 2010

Going through the code, we can identify six table fields that would benefit from an assossicated input parameter. They are:

• AggregatedField: Replaces FEE_NUMBER. It is the field on which the aggregate function will be applied.
• ColumnField: Replaces REGION_CODE. It is the field that is makes up the column data in the result set.
• RowField: Replaces the Month names field. It is the field that is makes up the row data in the result set.
• ColumnFieldAlias: Replaces “REGION_0X”. Sets the column headers.
• RowFieldAlias: Replaces “Month”. Sets the Rows headers in the leftmost column.
• SortRowField: Replaces the Month Numbers. Sets a field that will be used for sorting the row data.

The AggregatedField parameter poses a particular challenge, which makes it more difficult to implement than the others. For that reason, we will focus on the other ones today, and leave it for the next article.

Adding the remaining input paramters to the proc yields the following code. Again, the new fields appear in Red for easy identification:

CREATE PROCEDURE `p_case_counts_per_region_by_month` (IN AggregateFunction VARCHAR(64),
                                                      IN ColumnField       VARCHAR(64),
                                                      IN RowField          VARCHAR(64),
                                                      IN ColumnFieldAlias  VARCHAR(64),
                                                      IN RowFieldAlias     VARCHAR(64), 
                                                      IN SortRowField      VARCHAR(64))
LANGUAGE SQL 
NOT DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
BEGIN  
SELECT concat(   
  "SELECT CASE WHEN sort_row_field IS NULL", "\n", 
  "            THEN 'TOTAL'", "\n", 
  "            ELSE row_field", "\n", 
  "       END        AS '", RowFieldAlias, "',", "\n",
          group_concat(DISTINCT concat("       agg_fld_", ColumnField, 
                                       "  AS '", ColumnFieldAlias, ColumnField, "',", "\n"
                                )
                       order by ColumnField
                       separator '' 
          ),
  "       TOTAL", "\n",
  "FROM  (	SELECT	", SortRowField, "\t\t\t\t\t\t\t\tAS sort_row_field," , "\n",
  "\t\t", RowField, " \t\t\t\t\t\tAS row_field,", "\n",  
  group_concat( 
  DISTINCT concat("\t\t\t\t", AggregateFunction, "(CASE WHEN ", "REGION_CODE", " ='", REGION_CODE, 
                  "' THEN FEE_NUMBER ELSE NULL END) AS 'agg_fld_", 
                  REGION_CODE, "',", "\n"
           )
    order by REGION_CODE
    separator '' 
  ),
  "      	", AggregateFunction, "(FEE_NUMBER)\t\t\t\t\tAS 'TOTAL'", "\n",
  "		FROM  TA_CASES", "\n",
  "		WHERE YEAR(CREATION_DATE)=", YEAR(CREATION_DATE), "\n",
  "		GROUP BY SortRowField WITH ROLLUP) AS CA;"
) statement
into @sql_stmt
FROM TA_CASES
WHERE YEAR(CREATION_DATE)=1998;
  prepare case_counts_per_region_by_month   
  from @case_counts_per_region_by_month;    
  execute case_counts_per_region_by_month;   
  deallocate prepare case_counts_per_region_by_month; 
END

Another enhancement to the proc was to change the field aliases of the inner SQL to utilize more generic naming. Hence, “REGION_0X” becomes “agg_fld_0X”, “Month_Num” has been updated to “sort_row_field”, and “Month” is now “row_field”. The outer query will always refer to these fields by these names, regardless of what alias names are passed in.

One of the perks of using dynamic parameters is that you are not limited to field names. Functions such as “MONTH(CREATION_DATE)” and expressions such as “Days / 4” are equally valid. We can now supply both the “MONTH(CREATION_DATE)” and "MONTHNAME(CREATION_DATE)" functions to our proc:

mysql>call p_case_counts_per_region_by_month("COUNT", 
-->   "FEE_NUMBER ", 
-->   "MONTHNAME(CREATION_DATE)", 
-->   "REGION ", 
-->   "Month", 
-->   "MONTH(CREATION_DATE)");

Calling the proc with the above paramters reproduces what we generated previously:

Thanks to the changes that we made here today, we can now produce different reports by playing with the parameters. Here’s a report of Citizenship Codes by Region:

mysql>call p_case_counts_per_region_by_month("COUNT", 
-->   "FEE_NUMBER", 
-->   "CITIZENSHIP_CODE", 
-->   "REGION ", 
-->   "CITIZENSHIP CD", 
-->   "CREATION_DATE");

Note that the totals don’t add up because some rows were ommitted.

Next time, we’ll add a parameter to replace the “FEE_NUMBER” as well as include parameters to choose different tables and criteria.

Additional Resources

MySQL Stored Procedures in MySQL 5.0

» See All Articles by Columnist Rob Gravelle