Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Apr 7, 2010

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

By Rob Gravelle

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:

Month

REGION 01

REGION 02

REGION 03

REGION 04

REGION 05

TOTAL

April

13

33

76

2

47

171

May

17

55

209

1

143

425

June

8

63

221

1

127

420

July

13

104

240

6

123

486

August

18

121

274

9

111

533

September

25

160

239

2

88

514

October

9

88

295

2

127

521

November

2

86

292

2

120

502

December

1

128

232

6

155

522

TOTAL

106

838

2078

31

1041

4094

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");

CITIZENSHIP CD

REGION 01

REGION 02

REGION 03

REGION 04

REGION 05

TOTAL

(NULL)

7

13

28

4

60

112

26

0

0

12

0

0

12

33

0

0

0

0

2

2

44

0

0

11

0

1

12

45

0

1

5

0

2

8

48

0

0

1

0

0

1

52

0

0

0

3

0

3

56

0

0

1

0

3

4

59

2

0

0

0

0

2

60

0

0

0

0

2

2

81

0

0

22

0

7

29

83

0

0

2

0

2

4

5139

0

45

0

0

0

45

5205

0

5

0

0

0

5

602

0

0

2

0

0

2

650

0

0

0

0

2

2

651

0

0

1

0

0

1

654

0

0

0

0

15

15

703

0

1

0

0

0

1

711

0

0

10

0

0

10

722

0

0

1

0

0

1

979

0

0

1

0

1

2

999

0

0

1

0

0

1

TOTAL

104

838

2078

31

1041

4092

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



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date