Stored procedures are one of the key instruments that every rock star database design/developer uses
. Include stored procedures into your database and IT security officers will love you, network administrators will sing songs of praise about you. You will actually risk being in the hate list of SQL injection attackers. You will make life easier for other developers who provide supporting systems to your applications. End users will think you are a system performance miracle worker. In a layman’s language, stored procedures are packaged SQL statements stored in the database. They are also capable of receiving input parameters. The SQL statements can be anything from SELECT, INSERT, UPDATE, and DELETE statements
. The list of six (6) reasons below is for the non believers in the heavy metal-ness of stored procedures
Top 6 Advantages of Using Stored Procedures Explained
As the saying goes, too much of anything is not good, there are some factors that you should consider when using stored procedures
- Built In Access Control - When it comes to safe guarding information assets, authentication and authorization have different meaning. The fact that a user is authenticated does not mean he/she has the right to access all the resources. Stored procedures enable you to allow certain users to only interact with stored procedures and deny them access to the underlying tables. This will prevent users from modifying data from the tables directly. The side effect of this is the integrity goal of information will be maintained and you will make it to the A list of information systems auditors.
- Automatic parameterized of user input – chances are you have heard of prepared statements. Prepared statements allow you to generate the base SQL statement then supply the parameters. It doesn’t matter whether the parameters contain SQL Injection code or not, they will automatically be rendered useless for you. Stored procedures will do this for you without using prepared statements in your code. This is what will get you into the hate list of SQL injection attackers.
- Reduced Network Bandwidth usage – the more bandwidth sent over a network the slower the network becomes. Consider a SELECT SQL statement that does customer billing of more than 50,000 customers. You will have to put the records, process them then update the server. With stored procedures, you can include the business logic and process the bills on the server without pulling the records. This will significantly reduce the network bandwidth usage. Network administrators will love your app for this.
- Cost effect alternative to APIs – suppose your application has data that it needs to make available to third party systems such as payment gateways that have access to the database server. Instead of developing an API which is costly, you can take advantage of stored procedures to provide the required information to the third party systems. You can limit what they access and can do with your database.
- Improved query performance – this applies to frequently executed queries. The statements will be compiled and stored for the first execution. The subsequent query executions will take advantage of the compiled statements.
- Easily share business logic with other applications – if you encapsulate the business logic in the database, then it will be easy for other third party applications to use them directly. You can also wrap them in an external API such as REST to provide an interface to third party systems.
. You don’t have to write stored procedures for every table in the database. Here are the reasons why
Two 2 Disadvantages of stored procedures
- 1. Suppose you have a database with 27 tables, you will need to create, read, update and delete data from each table (special exceptions apply). Creating a stored procedure for each of these tables will create 27 * 4 = 108 procedures. In addition to the 108 stored procedures, you will also need to retrieve data from multiple tables; this can introduce roughly 50 more procedures. 158 stored procedures may become hell to translate to another database engine if need arises.
- Another concern of using stored procedures is SQL is not as powerful as fully developed languages such as Java and C#. You will be limited with what you can do.
Does this mean you shouldn’t use them at all? Not necessarily, the next section addresses this concern
When should you use stored procedures?
- You need to run scheduled tasks – stored procedures can be scheduled to run only once a day. This could be useful in archiving and initializing certain daily tables
- You need to share un-complicated business logic with other applications – this comes in handy if you are writing systems that post payments, bill customers or perform some analysis on the data that can be accomplished with SQL statements and can be reused by third party systems.
- You need to control access to certain tables based on user ids – your database may have tables with sensitive data that you may not want certain users to have direct access. You can implement stored procedures for such tables
- You need to process data from tables with a lot of records over a network with limited bandwidth – stored procedures will allow you to execute such tasks directly on the database server
- You have enough resources on the server – stored procedures transfer the processing to the server. Before using stored procedures, you need to make sure that you have enough resources on the server to handle the load.
The next articles will show you how to implement stored procedures in MS SQL Server and MySQL. Subscribe to our weekly newsletter by filling your email address in the text box below so that you get all the updates delivered directly to your mail box free of charge. Support us by liking and sharing our articles in social sites.