MySQL Input Parameters Add Flexibility to Crosstab Stored Procedures

When generating a result set where the query contains an unknown number of column and/or row values we can use a combination of Prepared Statements, which allows us to tailor the output based on the number of data values. We can also add input parameters to a procedure to assign the field names, aliases, and even the aggregate function!

In the previous article of the MySQL crosstab series, entitled MySQL Prepared Statements to Generate Crosstab SQL, we worked on generating the result set where the query contains an unknown number of column and/or row values. Our solution was based on a combination of Prepared Statements, as well as the concat() and group_concat() functions. This approach allowed us to tailor the output based on the number of data values, so that we could free ourselves from having to anticipate changes down the road. In today’s installment, we are going to take things one step further by adding input parameters to our proc, so that we can assign the field names, aliases, and even the aggregate function!

Recap of the Crosstab Stored Procedure

Let’s quickly revisit the stored proc that we created to dynamically generate the SQL code and result set. Inside the procedure, we generated the SQL for the query and saved it to a variable using the SELECT INTO syntax. A Prepared Statement was then utilized to execute the generated code:

CREATE PROCEDURE `p_case_counts_per_region_by_month`() 
LANGUAGE SQL 
NOT DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
BEGIN  
  SELECT concat(
    "SELECT CASE WHEN Month_Num IS NULL", "n", 
    "            THEN 'TOTAL'", "n", 
    "            ELSE Month", "n", 
    "       END        AS 'Month',", "n",
    group_concat( DISTINCT concat("       REGION_", REGION_CODE, 
                                  "  AS 'REGION ", REGION_CODE, "',", "n"
                           )
                  order by REGION_CODE
                  separator '' 
                ),
    "       TOTAL", "n",
    "FROM  (	SELECT	MONTH(CREATION_DATE)ttttttttAS Month_Num,", "n",
    "ttCONVERT(MONTHNAME(CREATION_DATE) USING latin1)tttttttttAS 'Month',", "n",  
    group_concat( 
        DISTINCT concat("ttttCOUNT(CASE WHEN REGION_CODE ='", REGION_CODE, 
                        "' THEN FEE_NUMBER ELSE NULL END) AS 'REGION_", 
                        REGION_CODE, "',", "n"
                 )
        order by REGION_CODE
        separator '' 
    ),
    "      	COUNT(*)tttttttttttttAS 'TOTAL'", "n",
    "		FROM  TA_CASES", "n",
    "		WHERE YEAR(CREATION_DATE)=", YEAR(CREATION_DATE), "n",
    "		GROUP BY Month_Num WITH ROLLUP) AS CA;"
  ) statement
  into @case_counts_per_region_by_month
  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

Running the proc confirmed that it produced the desired results:

mysql> call p_case_counts_per_region_by_month;

Defining the Input Parameters

All of our input parameters will be of type VARCHAR so that they can accommodate strings of variable length. For the sake of simplicity, we’ll assign all of our parameters a size of sixty four, which happens to be the maximum field name length in MySQL, as the following chart affirms:

The AggregateFunction Input Parameter

The AggregateFunction parameter is an especially interesting one in that it will allow us to apply different kinds of calculations on our data. MySQL supports ten such functions:

Here is what the modified proc looks like with the input parameter. The new code is highlighted in Red:

CREATE PROCEDURE `p_case_counts_per_region_by_month` (IN AggregateFunction VARCHAR(64)) 
LANGUAGE SQL 
NOT DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
BEGIN  
  SELECT concat(
    "SELECT CASE WHEN Month_Num IS NULL", "n", 
    "            THEN 'TOTAL'", "n", 
    "            ELSE Month", "n", 
    "       END        AS 'Month',", "n",
    group_concat( DISTINCT concat("       REGION_", REGION_CODE, 
                                  "  AS 'REGION ", REGION_CODE, "',", "n"
                           )
                  order by REGION_CODE
                  separator '' 
                ),
    "       TOTAL", "n",
    "FROM  (	SELECT	MONTH(CREATION_DATE)ttttttttAS Month_Num,", "n",
    "ttCONVERT(MONTHNAME(CREATION_DATE) USING latin1)tttttttttAS 'Month',", "n",  
    group_concat( 
        DISTINCT concat("tttt", AggregateFunction, "(CASE WHEN REGION_CODE ='", REGION_CODE, 
                        "' THEN FEE_NUMBER ELSE NULL END) AS 'REGION_", 
                        REGION_CODE, "',", "n"
                 )
        order by REGION_CODE
        separator '' 
    ),
    "      	", AggregateFunction, "(FEE_NUMBER)tttttttttttttAS 'TOTAL'", "n",
    "		FROM  TA_CASES", "n",
    "		WHERE YEAR(CREATION_DATE)=", YEAR(CREATION_DATE), "n",
    "		GROUP BY Month_Num WITH ROLLUP) AS CA;"
  ) statement
  into @case_counts_per_region_by_month
  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

We can now easily manipulate the data using different aggregate functions, such as SUM():

mysql> call p_case_counts_per_region_by_month("SUM");

…the STDDEV() function, which calculates the standard deviation:

mysql> call p_case_counts_per_region_by_month("STDDEV");

…or the MAX() function, to tell us which case had the largest FEE_NUMBER:

mysql> call p_case_counts_per_region_by_month("MAX");

Obviously, it would be beneficial to be able to apply the functions to other fields, so let’s add parameters for them as well.