5.3.3 The rewards_report Stored Procedure

Description

The rewards_report stored procedure generates a customizable list of the top customers for the previous month.

Parameters

• min_monthly_purchases: The minimum number of purchases or rentals a customer needed to make in the last month to qualify.

• min_dollar_amount_purchased: The minimum dollar amount a customer needed to spend in the last month to qualify.

• count_rewardees: An OUT parameter that returns a count of the customers who met the qualifications specified.

Return Values

This procedure produces a table of customers who met the qualifications specified. The table has the same structure as the customer table. The procedure also returns (in the count_rewardees parameter) a count that indicates the number of rows in that table.

Sample Usage

mysql> CALL rewards_report(7,20.00,@count);
...
| 598         | 1        | WADE        | DELVALLE     | [e-mail]         | 604        | 1      | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 |
| 599         | 2        | AUSTIN      | CINTRON      | [e-mail]        | 605        | 1      | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 |
...

42 rows in set (0.11 sec)

Query OK, 0 rows affected (0.67 sec)

mysql> SELECT @count;
+--------+
| @count |
+--------+
|     42 |
+--------+
1 row in set (0.00 sec)