Small methods - Small sprocs

Uncle Bob recommends having small methods. Do stored procedures have an ideal size? Or can they run on for 100's and 10开发者_StackOverflow社区0's of lines long?

Also does anyone have anything to say about where to place business logic. If located in stored procedures, the database is being used as data processing tier.

If you read Adam Machanic, his bias is toward the database, does that imply long stored procedures that only the author of the sproc understands, leaving maintainers to deal with the mess?

I guess there is two inter-related questions, somehow.

Thanks in advance for responding to a fuzzy question(s).

Stored procedures are no different than regular functions. They should be of manageable size, regardless. I am biased towards keeping away business logic from stored procedures but reasonable people may disagree.

I think stored procedures nowadays should NEVER be used on a whole system as the only access method to the database. This is an outdated architecture that in the long-term gives much more maintainance problems than benefits. There are much better ways nowadays to handle every data access requirement. The best use for stored procedure is for certain rare cases when you want a single, well defined and unique function to retrieve data that you know it will be used in the same way by more applications. The stored procedure will allow you to be DRY in this case. Also in certain cases where your db administrator that handles security needs to protect certain part of the data (for example a credit card table) on such a granular way that allowing access only to SP is a good option.

Apart from those cases avoid stored procedures as much as possible and stick with only using code with all the benefits of inheritance, compilers checks, tools for refactorizations, enumerations instead of magic strings also in queries, source control, easier deployment etc etc. The list of benefit of avoiding SP as much as possible is just too long to pass nowadays.

BUT if for some reason you decide to use stored procedures you might as well put business logic in there as having such a layer so close to the data without even allowing it to contain business logic will just further complicate your project and you will not reap the very few positive points of using SPs.

I would apply the same recommendations to SP's as much as possible as I consider SP's code.

Business Logic in my opinion belongs in a tier of the code base not in SP's. To me if the SP's keep the business logic they know too much about what they are supposed to do. I think SP's mainly should be tasked with when used for retreiving data and/or storing it. If business logic has been applied up the chain of command / in the code then SP's would only be called when business logic has been satisfied.

I doubt Adam Machanic or most would advocate that long SP's that are hard to understand and maintain are a good thing.