Best practices of structuring stored procedures

Question

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 code
  • In c# I refactor and break out smaller methods that does just one logical task each.
• Code duplication

Mostly I encounter SQL Server as DBMS but DBMS agnostic answers or answers pointing out features of other DBMS:es that help in above cases are also welcome.

To give some background: Most large stored procedures I have encountered are in reporting scenarios where the base is to just create some summary values from a large table. But along the way you need to exclude some of the values that happen to be in some exception table, add some of the values in some not yet completed stuff table, compare with last year (can you imagine the ugly code that handles products changing department between years?), etc.

Answer 1

I write a lot of complex stored procs. Some things I would consider best practices:

Don't use dynamic SQl in a stored proc unless you are doing a search proc with lots of parameters which may or may not be needed (then it is currently one of the best solutions). If you must use dynamic SQl in a proc always have a debug input parameter and if the debug parameter is set, then print the SQL statement created rather than executing it. This will save hours of debugging time!

If you are performing more than one action query in a proc (insert/update/delete), use Try Cacth blocks and transaction processing. Add a test parameter to the input parameters and when it is is set to 1, always rollback the entire transaction. Before rolling back in test mode, I usually have a section that returns the values in the tables I'm affecting to ensure that what I think I am doing to the database is in fact what I did do. Or you could have checks as go as shown below. That is as simple as putting in the following code around your currently commented out selects (and uncommenting them) once you have the @test parameter.

If @test =1 Begin Select * from table1 where field1 = @myfirstparameter End 

Now you don't have to go through and comment and uncomment each time time you test.

@test or @debuig should always be set with a default value of 0 and placed last in the list. That way adding them won't break existing calls of the proc.

Consider having logging and/or error logging tables for procs doing inserts/updates/deletes. If you record the the steps and or errors in table variables as you go, they are still available after a rollback to be inserted into the logging table. Knowing what part of a complex proc failed and what the error was can be invaluable later on.

Where possible do not nest stored procs. If you need to run multiple records in a loop, replace the stored proc with one that has a table-valued parameter and set up the proc to run in a set-based and not individual record fashion. This will work if the table-valued parameter has one record or many records.

If you have a complex select with a lot of subqueries or derived tables, consider using CTEs instead. Refactor any correlated subqueries or cursors to better performing set-based code. Always think in terms of sets of data not one record.

Do not, under any conceivable circumstance, nest views. The performance hit is much worse than any small amount of saved development time. And trust me, nested views do not save maintenance time when the change needs to be to the view the furthest into the chain of views.

All stored procs (and other database code) should be in source control.

Table variables are good for smaller data sets, but temp tables (real ones that start with # or ## not staging tables) can be better for performance in large data sets. If using temp tables drop them when you don't need them anymore. Try to avoid the use of global temp tables.

Learn to write performant SQL. It is usually just as easy to write SQL that will perform well than SQL which will not once you know the techiniques. If you write complex stored procs, there is no excuse for not knowing which techniques work better than which other ones. Learn how to make sure your query is sargable. Avoid cursors, correlated subqueries, scalar functions and other things which run row-by-agonizing-row.

Answer 2

Communication via temp tables is sometimes a huge code smell. Such procedures often cannot be run by a user without interfering with each other (if you re-use a temp table name for different procedures' ins and outs and they aren't re-created or if you use the same name with two different table schemas). They can be hard to troubleshoot - like any feature, use them when necessary and better alternatives don't exist. Using real tables temporarily can also be problematic.

Stored procs which pass data to each other in SQL Server at all (more than parameters) can be problematic. There are table-valued parameters now and many things which previously would have been done with procs can now be done with inline table-valued functions or (and usually preferred over) multi-statement table-valued functions.

In SQL Server, avoid heavy use of scalar functions and multi-statement table-valued function on large rowsets - they do not perform very well, so modular techniques which may seem obvious in C# don't really apply here.

I would recommend you look at Ken Henderson's Guru's Guide to SQL Server Stored Procedures - published in 2002, it still has a wealth of useful information on database application design.