MSSQL - benefits of using stored procedure and function

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

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. Difficult or no way for the caller to bypass the audit log process.s

• Able to implement sophisticated controls that include security checking. Meaning that, before updating the record, you may want to ensure that the user has the permission in updating the record or the caller's IP address is in the IP white list.

• Easier to limit the number records to be returned and renaming the field name. For example, if the caller wants to view all member records and you have more than a million members, calling select * from tb_member will impact other process, causing massive I/O and occupying more memory.

• Easier to modify/process the records before returning the records. For example, returns the 'amount' where the amount is 'quantity x unit price'.

• Stored procedure has been compiled and it runs faster than the ad-hoc query. Notes: not all database engine support this idea. For example, Postgres might not have much difference in running adhoc query vs stored procedure.

• 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 stored procedure.

• Easier to execute insert into multiple tables with one calls.

• Query optimization can be carried out at any time.

• Because of the stored procedure implementation detail (i.e., the codes) are kept in sys.syscomments, you can run a search against this table and see how many stored procedure is relying on a given object/table. This will ease the code maintenance.

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.

Back to #MSSQL blog

Back to #blog listing