Database stored procedure development guide

Published on: 26th Jun 2012

Updated on: 15th May 2021

Overview

In the past, we have developed many desktop programs with Visual Basic 6 (VB6) which rely on MS Access database. We were so used to writing all queries in VB6. After a few years, we were moving towards the client and server architecture and the database engine has been replaced by MSSQL. From that day onwards, we have set up a new guideline by not writing any complex query into the program (EXE).

A little bit information on why moving to MSSQL

The main reason for this move is to cut down the network traffic for each query. The network latency is a factor to consider during the performance test. For example, the user clicks on a monthly customer statement report. If the necessary data is to be sent to the desktop for processing, summarizing, etc, this will put pressure on the network workload. Don't talk about multiple users making the same requests.

The second reason is to avoid recompiling the EXE whenever there is a new requirement or bug fixing. Some of the new requirements can be done very quickly if the process was developed in SQL server. We just need to patch the stored procedure. For bug fixing, this is harder to avoid recompiling the EXE but if the data process was coded in a stored procedure, we will be able to reduce the chances of recompiling the EXE.

90% of projects that we have done were line of business (LOB). New requirements were coming in very often due to the business environment. Recompiling the EXE and updating all workstations requires users to stop their work and wait for the upgrade. If the new requirement or bug fixes can be done in stored procedure, we will patch on the MSSQL server which does not interrupt the user activity. This is a better way to install the patch.

Our guideline in developing stored procedure

• The stored procedure must be testable & traceable.

  • The stored procedure that we developed will have a @is_debug int = 0 parameter with the default value of 0. Upon testing the stored procedure, we will pass in @is_debug = 1 which PRINT the runtime values that we are interested in. This is very helpful in the tracing and fine tuning process.

  • Aside from the PRINT keyword, application log and audit log must be created for system support to trace the process or at least giving some ideas on what has happened.

• The stored procedure must be broken down into smaller pieces of the process.

  • It's difficult to maintain any stored procedure which has 1000+ lines (excluding the blank lines and comments). This is not a good way to develop stored procedures because it is difficult to do quality control and upgrading will be difficult as well. The best way is to modularize the processes by breaking it down into multiple smaller processes. Of course, this is not a hard rule.

• Not all the stored procedures must be able to re-run.

  • We should not expect the stored procedure will be running once. It should be tested with the idea of what-if the caller runs it for multiple times. What will happen? Another way of thinking is that, if we run the same stored procedure multiple times with the same parameters, does it give the same output?

  • In some cases, a stored procedure might be run once a day. For example, this is true for calculating the overdue interest. In this case, the stored procedure itself must have a way to prevent re-entrant. It should create a log somewhere to record down the last run date/time and use it as an indicator whether it should run again.

• Stop hard-coding the constants into a stored procedure

  • Some programmers want to complete their job as soon as possible without really caring about future maintenance. The best way to handle constant is to save the value in a table and load it when you need it.

• Naming convention

  • All stored procedures must follow the agreed naming convention. In fact, the stored procedure name should contain the main module code (as prefix) to ease the future maintenance.

    For example, in the inventory control module, there is a process that helps to calculate the latest quantity on hand. In this case, you may name the stored procedure like this:

      pr_ic_update_qoh @current_uid, @prod_id, @qty, @is_debug
    

    Where

    • pr - standards for procedure. Some other developers named it 'up' (user procedure) but I find it difficult for new developers to guess who is 'user'.. is it the end user?!
    • ic - is an inventory control module.
    • update - to indicate it is an updating process.
    • qoh - standards for quantity on hand.

    Let's say there is another module called point of sales (POS) and you are developing the sales posting process. Here is how it should look like,

      pr_pos_gen_receipt @current_uid, @receipt_id output, @is_debug
      pr_pos_save_item_line @current_uid, @receipt_id, @prod_id, @qty, @is_debug
      pr_pos_finalize @current_uid, @receipt_id, @is_debug
    

    Where

    • pr_pos_gen_receipt - this procedure is responsible for generating a new receipt number and a receipt primary key value.
    • pr_pos_save_item_line - this procedure is responsible for saving the item line (i.e., the item bought by the customer) and then calling pr_ic_update_qoh .
    • pr_pos_finalize - this procedure is finalizing the entire sales posting process. Such as, creating an audit log.

• Avoid using TRIGGER

  • Trigger is a convenient way to process the new data automatically behind the scenes. From the application development point of view, the problem with triggers is that you are not able to find out who is the current user.

    This happens when we are developing a web application and the application uses only one SQL user ID for all queries. The SQL user ID is stored in the database connection string (in the application configuration file).

    In other cases where the user ID is coming from OAuth or another security server. Trigger will not be able to find out who is the current user.

The above guideline is a summary on what we have. Things that are not mentioned above will be more on the coding convention. Contact us if you have any comments.

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.