Benefits of using stored procedure

Published on: 1st Oct 2012

Updated on: 18th May 2021

Overview

Starting from my favorite programming language, Visual Basic 6 (VB6) (that was in 1997), I've been thinking where is the best place to implement the "business rule". With VB6, you may implement business rules very quickly. But, if you made a mistake that caused a bug, then, you will have to recompile the program and update all the workstations. It's quite tedious.

After many years of moving the business rules in between the programming language and database, in 2012, I have decided the best place for the business rule should be in the stored procedure and not in SQL trigger.

Why stored procedure

A stored procedure can handle all these operations: SELECT, INSERT, UPDATE and DELETE. In fact, you can do more than that. Benefits of using the stored procedure,

• Create an audit log when doing any of the CRUD (create, retrieve, update and delete) operation.

• Able to implement sophisticated controls that include security checking.

• Able to control the records to be returned including renaming the field name.

• Easier to modify/process the records before returning the records.

• Stored procedure has been compiled and it runs faster than the ad-hoc query.

• Able to enhance and update the stored procedure at any time without asking the user to stop their works during the update.

• You don't have to recompile the application EXE if there are minor changes in the rule. The exception is that if there are massive changes in the rule, you will still requires recompiling the application EXE.

• You don't have to redistribute the application (at client workstation or web server) if there are any changes in the rule.

What have we achieved with stored procedure

Added on: 18th May 2021

Starting from 2008, we have focused on web application development rather than desktop application development. With the move to use stored procedure, we have achieved the following

• Our web application backend program has been slimmed down tremendously and it became more like an infrastructure that connects the JavaScript at the front end to the database. The backend codes merely pass the user data to stored procedure and passing the stored procedure result back to the user.

• The application maintenance is focused more on the stored procedure and then JavaScript front end.

• Query performance tuning can be done in a shorter time frame because we are focusing on database side only. Most of the requests can be completed in less than 3 seconds. As for the front end, it does not do much in the browser. In fact, the front end is only showing the data that it received from the web server.

• The user session has also been moved from C# to the database stored procedure. This allows us to have a better control on the user session and permission checking.

A little bit of my history: we have focused on C# since 2008, and we put more focus on Node.js on Ubuntu since 2020.

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.