Before we go any further, let's look at some general principles for designing good stored procedures.
- As with any other programming language รข€“ keep it simple. Don't make your procedure complex to read and understand if it is only going to save a few microseconds of processing time. If a stored procedure is complex to understand, then applying bug fixes or upgrading will need a formidable effort.
- Document your code, especially if you have to create a complex section of code. Throughout the stored procedure, place comments with a description of the stored procedure and any changes made at the top. This is even more crucial if the source control system that you are using doesn't have this ability.
- At processing time, T-SQL works well with a set. Therefore, avoid cursors wherever possible; even if it means using two or three steps or maybe even a temporary table.
- Don't rule out cursors totally, though. There will be times, although relatively rare, when they will be more beneficial to the overall processing.
- If you pass parameters, then ensure that the values are within an acceptable range, especially if the parameter is optional and a value of NULL is permissible. If this is the case, then keep results in mind when this parameter forms part of a WHERE filter or a JOIN criterion.
- Always clean up. If you have used a cursor, close and deallocate it, so that memory and resources are freed up. Similarly, if you are using temporary tables, ensure that you explicitly drop them. A temporary table defined within a stored procedure can exist only for the lifetime of that process. However, if the stored procedure has been called from a parent process, and if it is an iterative process, then the temporary table may exist beyond the lifetime of the process.
- By dropping the temporary table explicitly, even if the code is moved from the stored procedure and run as standalone code within Query Analyzer, it will ensure that the table is cleared up. If you do not do this, then the temporary table will remain until the Query Analyzer session is closed, the temporary table is dropped through T-SQL code from within that Query Analyzer session, or SQL Server is recycled.
- When a stored procedure is complete either through an error or a successful process, ensure that you return a value. This is a simple method of checking that the stored procedure has terminated as you were expecting, and if not, then you can then deal with the problem within the calling procedure.
