Welcome to ciysys blog

Database stored procedure development guide

Published on: 26th Jun 2012

Updated on: 15th May 2021


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 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


Lau Hon Wan, software developer.