More a short rant today than anything else. If you are writing a DAL most of it can be generated and be completely boilerplate. Everything looks the same, acts the same, is called the same way.very beautiful.
There's always exceptions, however. Sometimes you just NEED to do something more complicated. Inserting into multiple tables at once, possibly across different databases.
The project I'm working on has a very consistent way of putting database operations in transactions. I've been troubleshooting a number of problems in the app where the app will crash after trying to save some records. The proc seemed to be working, it was returning a new id and everything was happy. Why couldn't the app find these records?
Because the stored procedures fall under the exceptions to the rule above and the developer of them thought it would be best to do transaction handling inside the stored procedure.
Two problems, one the transactions across the linked server didn't work because of configuration issues. Secondly, the stored proc did a try . catch . rollback without calling RAISERROR.
I certainly think that transactions inside the stored procedure can clean up some of the client code, and help in situations when the stored procedure can be called in many different places and always needs to be transactional. Just keep in mind that unless we let the calling client know something went wrong it's just as bad as swallowing exceptions in C# or VB. Fail fast and hard, as always.