Why use Stored Procedures?

Introduction

A lot of people are now using stored procedures for their database operations, but what is so good about them? This article accompanies 'Developing Stored Procedures' and looks at the advantages and disadvantages of stored procedures. Note that most of the features listed here depend on the database server you are using.

Performance

If you have a really complex query then stored procedures will speed up the query after the first run (much like ASP.NET pages). This is because, much like ASP.NET, it generates an execution plan for the procedure. This is like taking your VB.NET code and moving it to MSIL code, it is faster and quicker to run. The DB Server will automatically update this plan when changes to the procedure are made.

Encapsulation

This ties in with Performance because you can take multiple statements (eg. an INPUT, UPDATE and SELECT) into one procedure, this significantly reduces the code needed to type in when using those statements (eg. on an ASP.NET page) and the 'compiling' done to the procedure make it faster to execute.
Parameterized Queries
Using input/output queries, you can customize the way that your queries operate, like functions and methods in regular programming languages that allow you to input parameters to change the output, stored procedures allow you to do the same.

Maintainability

When you write a stored procedure, applications can call it whenever they want and you can update the procedure whenever you see fit and the new version will be used by everyone who used it before and they don't even have to know it. This is especially good if a new requirement comes up and you already have 5000 people using it, you can just update the SP and everyone seamlessly uses the new one (unless you screw it up and it causes them all to fail).

Security

With a regular SQL statement, security would have to be set by what a user can and cannot do to the database or table. This can be very restricting and tedious if you want to update the security for a user or add a new user to the list. Depending on your database server (I know that Oracle and SQL Server can do this) you can set up permissions for individual stored procedures. You could lock down the database and have stored procedures the only way to access the database if you wanted to be than conscious.

Easy to learn and copy

Stored procedures are just SQL and are very easy to learn and to copy. You can find resources on creating them nearly anywhere on the web and tutorials on SQL as well. You can also copy them easily from database to database (or database server to database server). If you have the MSDN Library CD's then you will find everything you need to know about programming the stored procedures using T-SQL.

Easy Debugging

This really only applies to users of VS.NET, but you can interpret error messages if you want.
VS.NET uses it's debugging tools to debug T-SQL stored procedures so that you will always get it right. There may be some other debugging tools out there (there must be) and those should also provide the features to debug your stored procedures.

Disadvantages

As you can see, there are so many advantages from using stored procedures that you're probably wondering how terrible the bad stuff it. The truth is - it isn't. As far as I can see, they are nearly perfect for your database, but sometimes they require some work to create and debug them (especially if you are new to SQL programming), the only other thing that I can see if that there is not universal language for stored procedures. i.e. There is T-SQL for SQL Server and PL/SQL for Oracle, these two languages are quite different.

Summary
You can see that the advantages far outweigh the disadvantages (if you really call those disadvantages). So, why not use them? Check out the RELATED ARTICLES section for more information on creating and using stored procedures.