Stored procedures or inline SQL

The question is to be or not to be?

Right from the university days I have been tought that stored procedures are the best thing that can happen to a web developer. The reason why stored procedures are being advocated to students who are just into the field is unknown to be and I have never tried to explore them. Following are the reasons that are cited to them why to use stored procudures, but first let us know what is stored procedure.

Stored procedure are a very useful feature in SQL server. A stored procedure is a group of SQL statements that are compiled together and can be executed by calling a single command.

Uses for Stored Procedures:

1) Modular programming
2) Fast Execution
3) Network Traffic
4) Security

Sounds exciting when you read the book, but in practical, the picture is not as good as is depicted. I know of several projects, that are being developed with consideration of utilising the objects and/or modules from the project later on into another project. This is exactly why developers have implemented object oriented programming, and this is the reason, why .net has gained so much of popularity.

Moreover if the project is developed using n-tier architecture, then it would be more advisable to completely separate out the database from the rest of the modules. I have been wondering in the last project, about what is feasible, writing stored procedures or using the DAL(Data Access Layer) in the .NET architecure? Well technically both are same things, the former being stored on Data layer, which passes the data to the Business Logic Layer; and the later one being stored on Business Logic Layer, and accessing the data in the Data Layer!

Considering security, both are equally secure, because the system does not allow access to any processes outside the system to access the functions from the DAL. So what makes stored procedure, always a sought after option! I have been reading a couple of blogs recently regarding the same issue, and have found that the programming world has been divided into two segments on this issue! Those who recommend SPs and the ones that find them obsolete. Though there are many developers just like me, who are yet to make up their minds on whether to go for it, of its time to ditch them completely.

So far I have come across so many blogs that advocate both the approaches, that it is impossible for me to make a stand. But what I have learnt is both the camps are correct, in their ideologies. It all depends on the project and the approach a developer wants to implement!

For developers like me, who wish to implement the same module over and over again, without worrying for the database technology underneath; I recommend they should go for DAL. If you are worrying about the performance, leave it till the end and develop the whole project using DAL. You can tweak the bits and bobs here and there, later on to enhance the performance. If the purpose of the website is reporting, then you dont need security anyways, as you have the built in user profiling, and access control. If your website is not kind of reporting tool, then you dont have necessarily a performance issue, as the database connection is not going to be that frequent! so in either case, you dont justify the compulsion of using stored procedure.

I know there are issues related to using inline SQL commands, but then using stored procedure is not the only way to deal with it. If you would like to have a more detailed read, you can visit Frans Bouma's blog