As a developer mainly writing c# I have adopted some good practices when writing c# code. When I sometimes write stored procedures I have trouble applying those practices to the stored procedure code.
On several occasions I have inherited nightmare stored procedure code, first three or four layers of stored procedures setting up some temp tables and mostly calling each other. No real work done and just a few lines of code. Then at last there is a call to "the final" stored procedure, a big monster of 3000-5000 lines of SQL code. That code usually have a lot of code smells like code duplication, intricate control flows (a.k.a. spaghetti) and a method that does too many things stacked after each other with no clear separation where one chunk of work starts and where it ends (not even a comment as a divisor).
I have also noticed the use of out commented select statements that selects from intermediate temp tables. The selects can be turned back on for debug purposes, but need to be removed before any calling code expecting a specific order of the returned result sets.
Apparently my fellow team mates also share my lack of good SQL writing practices.
So... ( and here comes the real question) ... what are good practices for writing modular maintainable stored procedures?
Both home made practices and references to books/blogs are welcome. Methods as well as tools that help with certain tasks.
Lets summarize some areas where I have not found good practices
- Modularization and encapsulation (is stored procedures communication via temp tables really the way to go?)
- In c# I use assemblies, classes and methods decorated with access modifiers to accomplish this.
- Debugging/testing (better than modifying the target of debugging?)
- Debug tools?
- Debug traces?
- Test fixtures?
- Emphasizing code/logic/data/control flow using code the structure of the