So - how big are your sprocs? Do you worry about putting too much logic in your database? Why do you make them the size they are (because it is a complex project isn't a good answer)?
Now, we have projects (two) on .net2k3 (c#, web app) with crystal reports. Here the role of the SP is to fetch the data from more than 11 tables and put it in one #table and use it in CR as reports data (huge) and no.of SP lines goes to nearly between 1000
line to 1250. We have such reports 3.nos. And other SPs are all small 50 to 150 lines.
Well that is nothing.... In 1999, we had a project on vb6.0 and sql-6.5 (windows app, client-server), where SP lines goes to 2K to 2.5K and we had nearly some 450 procedures (only some were small).
Because, size is not the fact to keep in mind. If 10 programmes, 10 diff ways. So the ultimate is to get the output as desired that serves our purpose. And there is no other great way other than this (as i see). I can't open some 100s of recordsets and hold
the data and put into the grid in front-end . SQL statements need to be very very long, with all joins and stuff (which really helps in reducing the lines in SP)
And to all these, we need to add comments [C] to the lines so understand whats happening. So SP lines really increase more. Finally we break the SP into parts to 2 or 3 max and call them in one SP. This really helps in debugging or any later modifications.