Stored procs should be ignorant, obedient and just plain down right stupid.  We don't want them to ask questions, make decisions or do anything other than what we tell them.  If they start doing anything other than CRUD operations, then you can quickly find yourself in a whole world of T-SQL pain.

What am I talking about here?  The database making business decisions.  As soon as you see an 'IF' statement or a some sort of LOOP, or the god-forbidden 'GOTO' statement in a stored procedure you know your data access layer is doing more than it should.

Keep all business logic, validation and decision making in your domain/business layer - not in the database.

Why?  Many people will say its much easier and quicker to update stored procedures and release them to production.  No compiling, just a simple 'Execute' on the db server.  What this is telling me though, is that their release procedure and build environment could be improved dramatically. Try introducing a Continuous Integration server, and a standardised build/release procedure.

But aside from that, the most important thing you are giving up by placing your domain and business logic in stored procedures in testability and maintainability.  Ever tried finding a complex bug in a huge stored procedure?  I'd quickly rather bash my head against a wall than to put myself through that.

Keep all your business logic in an application coding environment (.NET, Java, C++) where you have such luxuries as refactoring tools, intelli-sense, integration with testing and mocking frameworks, logging tools, O/R mappers.  Create objects with methods.  When these objects require data, they can ask the database to provide it - but thats it - don't let the database make any decisions on your behalf, just make it do as its told.

Keep those stored procs stupid!

---

---