I made extensive use of stored procedures that created temporary stored procedures for reporting in a data mart application. This reason I did this was because there could be literally hundreds of different variations of the same basic report summarized along different dimensions and levels. The input parameters to the stored procedure specified the grouping/sorting levels that were required, and the stored procedure used that to generate the code.

It was easier to write one fairly complex procedure than it was to create hundreds of almost identical, simpler procedures for the different variations in the reports. The biggest advantage was that if there was a bug, I only had to fix it in one procedure instead of hundreds.