Best practices of structuring stored procedures

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.

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.

This is such a good question. As a C# dev myself having to dabble in SQL it seems SQL by its very nature gets in the way of the best-practices I'm used to with C#.

Common Table Expresions are great to isolate queries in a stored procedure but you can only use them once! That leads you to define views but then you've lost your encapsulation.

A resultset from one stored procedure are very difficult to use in another so you might be tempted to write table-valued functions. That adds to your permissions-maintenance burden and forces you to write functions 'twice' - once as a function and another as a procedure that calls the function. Otherwise, you have different interfaces to your DAL depending on whether it's a procedure or not.

All of this has caused me, over time, to stick to simple CRUD stored procedures (that do not call each other) in the database and few, isolated, queries when the relationships are complex. More BI-stuff. Everything else is in the BLL.

Physically, SQL is isolated in seperate files by function or the table they revolve around and managed in source control.

Avoid SELECT * and favor specifying columns. That saves you from run-time problems when you change a table and don't touch all the procs. Yes, there is a recompile for procs but it WILL miss some, especially if views are involved. Plus, SELECT * almost always returns more columns than you really need and that's a waste of bandwidth.

The comments above are great advice of Do's and Dont's when it comes to SQL code writing. If I understand your questions correctly, you are asking if this is normal for SQL Developer to write hundreds even thousands of code in a single stored procedure. In C# this is a big no-no. You are to encapsulate logic into small chucks using methods, assemblies, and classes. SQL Developer tend to write the entire logic in one stored procedure to accomplish a relating task; as HLGEM mentioned above, "If possible, do not nest stored procedures". Do not nest Views.

For example: A simple Get and Insert design in C# looks like this:

• Call GetData Method
• Call Get Data Method
• Call Transform Data Method
  • Call CheckAlphaNumeric Method
  • Call Data Enrichment Method
• Call Load Transformed Data Method

A SQL developer will design it like this: In a single stored proc:

• Get Data and Transform using either temp table or table variable, then Load it into the final table.

If you are to change the way SQL is written to match the writing structure of C# Developer, you would then do this:

• Execute Main Stored Procedure (which calls the below sproc.)
  • Execute GetData Stored Procedure and load into Stage Table
  • Execute Transform Stored Procedure which read the Stage Table and transform data
  • Execute Load Data stored procedure to load Staged or Transformed data into final table.