Depends on the client and their systems. The largest have been where they were pulling a lot of data from the 400. It seems that sprocs that pull data for proposals are always the largest.
In a perfect world I would like to keep my sprocs slim and trim. I would rather a client update a rule in an IDE then a sproc.
Do you worry about putting too much logic in your database?
No because sometimes when pulling data from other systems you dont want to add logic to your application that does not belong (I guess this is more of a design preference)
Why do you make them the size they are (because it is a complex project isn't a good answer)?
Because the calling application may not be able to manipulate the data. Also to keep bad table design join complexity in the sproc instead of branching out into the enterprise apps.