yes and we also have numerous versions of each "Report" sproc.

Our UI is a bit strange in that the users can pick a sorting and grouping option in the UI. this modifies the eventual output in Crystal.

We used to create dynamic SQL in a single sproc for each report based on the Sorting and Grouping options.

this was difficult to optimize on the SQL Server side.

so now, we create an xml file that lists all the reports by name, lists their parameters, their selects, and their sorting and group by options.

then the code gen process will create 1 version of the reporting sproc for each Sort\Group By combination.

There are TONS of sprocs in our DB, but they all run very quickly now. It's truely a dramatic improvement when compared to our old system.

I'm trying very hard to convince others here that we should migrate ALL grouping and sorting logic out of SQL Server and Towards the UI (Crystal) but so far I've not convinced the others on my team here that it's worth the effort.

hope this all makes sense....

GAJ