I’ve been thinking about the use of stored procedures lately.

Some questions that popped up in my head: What are the advantages/disadvantages? When and how should they be used? Will they extinct with LINQ to SQL becoming more and more popular? Why should you store your queries in your database, instead of in your code..

I asked the opinion of a few ASP.NET/.NET professionals. Hope you find this interesting!

Kris Van Der Mast (Blog)

Since I’m a consultant I have to go with the specifications of my clients. Most still use stored procedures though I see a shift occurring. My current client allows us to use Linq To Sql for select statements (generated by L2S) but for update, delete and insert statements they require us to make sprocs since they put security on it on a database level. My former client allowed us to use NHibernate (winforms project) but for 3-4 special occasions we used sprocs.

SyntaxC4 (Profile)

I do use Stored Procedures on a regular basis. I develop mostly in DotNetNuke, which has an extensive use of stored procs. I have read blogs, and forum posts that suggest that stored procedures shouldn’t be used as they can be a performance bottleneck, but I believe they are an excellent way of promoting SQL Abstraction. 

I do however try to preserve the number of stored procedures I write. I normally create one stored procedure to handle both insert and updates to the a particular table, one for deleting, and one to select (sometimes i do multiple select statements within one stored proc if it makes sense). 

To work away from bottlenecks, i try to use subqueries in my stored procedures instead of using a view to query against. unless of course a view is more efficient.

TATWORTH (Profile)

It is not much a case of would I use stored procedures as when would I not use them! Occasionally for searching, dynamic TSQL needs to be generated and for this I would use parameterized TSQL. Otherwise I use stored procedures all the time. I do have stored procedures for generating from the table definition stored procedures plus wrapper code. This allows very rapid development of the data layer.

The use of stored procedures (or the occasional parameterized TSQL) is in my opinion one of a series of essential steps to produce robust applications.

Tom Peeters (a co-worker)

I prefer to (read always) use stored procedures when I’m developing front end applications. 

The ones who find creating stored procedures is intolerable overhead, I want to give some advantages.. 

1. They are modular, I’d prefer to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.
2. They are tunable. Changes can be made to the stored procedures –in terms of join methods, differing tables, etc.– that are transparent to the front-end interface.
3. Stored procedures abstract or separate server-side functions from the client-side. It is much easier to code a GUI application to call a procedure than to build a query through the GUI code.
4. Stored procedures are usually written by database developers/administrators. Persons holding these roles are usually more experienced in writing efficient queries and SQL statements. This frees the GUI application developers to utilize their skills on the functional and graphical presentation pieces of the application. If you have your people performing the tasks to which they are best suited, then you will ultimately produce a better overall application.

In short, queries are best handled via stored procedures. While the initial development overhead is greater, you will more than make up for the investment down the line.

Looks like a lot of people are still very fond of stored procedures. The arguments make a lot of sense!