Stored Procedures are not evil!

Stored Procedures vs Dynamic SQL, Which one to choose?

There has been a long debate on the usage of stored procedures. Advocates of dynamic SQL present many reasons to avoid stored procedures. In this post, I will try to identify and explain the major reasons which compel us to use stored procedures instead of dynamic sql.


1. Separation of Logic
It is always nice to split your code in distinct modules and separate these modules physically. The logic pertaining to databases can be kept in sps. Next time, I need to change the logic, I do not have to download the latest code, setup application on my box (which can be a pain really), debug the application, and identify the relevant places in code. I can simply connect to the database server and check the logic in my sp.


2. Change Impact
Prepare the DB script- do not change your application files. I do not need to recompile my entire application and take care of the dependencies. It also saves me from a lot of work such as version management, application kit/installer preparation, etc. Also, changing Sps is easy. Suppose, pages X and Y are using sp A which generates same result set for both pages. Now page X wants to pass an additional parameter which will generate different rows in result set in sp A. All we need to do is to modify the sp code and make the additional parameter optional in sp A. Page X and Y will both work with X getting the updated result set.


3. Ease of Deployment
Deploying huge websites is not easy. I have worked in a company where we had more than 10 application servers running behind a load balancer in a cluster. Updating the application server was time-consuming and risky. However, I could prepare a DB scrip and run it. No "iisreset" is required and no down-time due to issues likes missing dlls, directory permissions, etc. The "server upgrade" activity remains hidden from users.


4. Usability
If more than one application are using the same logic, it is better to keep that logic in an sp. Lets consider an example here. Company A has products X and Y. X is a UI based application, used to maintain customer information. Y is a marketing tool which needs customer information for identifying target customers. We can write dynamic SQL in both X and Y which is a bad idea as it leads to the replication of code. We can add a webservice to X which Y can consume. This is again a bad idea as we are adding an additional layer here. The most appropriate solution is to write an sp which can be used by both X and Y.


5. Security
It is safer to keep business critical logic within database server instead of application server. Database servers are more secure and hard to hack or crack than application servers. When you put the logic in database, you are actually adding another layer of security for application users. Moreover, through sps you can not only provide row level security, but also column-level security on tables.


6. Performance
Why build the query everytime, marshall paramters, and waste bandwidth by sending long queries over the network? If you the know the sp name, all you need to do is to send the sp name with parameters. Similarly, a business operation that involves multiple calls to the database can be stored as an sp.


Lets consider an example here. Suppose, company A is giving a raise to every employee based on his/her last year performance. We have to update the Employees table, specifically the salary column. However, the raise will be different for every employee, so we cannot update the entire column by running an update statement like salary=salary*0.2. Through dynamic sql, we will have to fetch the entire employee table from the database, apply some processing at the application level, and send back the updated data set to update the database. A much better approach will be to implement an sp with "Salary-raise" logic. We can then simply call that sp and it will save us from receiving and sending back huge datasets over the network.


On a final note, Sps are already compiled and optimized by the database system. The difference in execution time will not be significant for simpler queries. However, if you have complex queries involving multiple tables, the execution time will turn out to be much lesser than that of dynamic sql.


7. Replication
Replication of databases is a piece of cake in modern database systems. Sps, like all other database objects, get copied without much hassel. Stored procedures can be scripted too. Therefore, replication at database level is much easier and faster than replication at application level.

I can present a number of other arguments in support of stored procedures. I may also write an extension to this post in future. But I hope, these points are sufficient enough to  prove that stored procedures are better than dynamic sql in most scenarios.

Cheers!
JS