Stored procedures have many advantages. They help you to separate the client application from the underlying structure of the database which allows you to simplify client coding, and improve the stability of the application. Furthermore, by using stored procedures, you can very easily create reusable code that can be executed from all of the applications that you and other developers write.
Stored procedures are very effective in terms of performance because they are pre-compiled and execute quickly. When you build a stored procedure, a query plan is created-which contains the most efficient method of executing the stored procedure given available indexes and other environmental factors. Another benefit of stored procedures is that they tend to lock data for shorter periods of time than the equivalent application code.
Stored procedures facilitate the security of data. This is because when you give users or groups rights to stored procedures, it is not necessary for you to give them rights to the underlying tables. A common scenario is to give users or groups view-only rights to tables. This way, they can build their own queries and reports. You then use stored procedures to add, edit, and delete data. Once you give users or groups the rights to the stored procedures, it is not necessary to give them add, edit, and delete rights to the underlying tables.
You can use stored procedures in either a two-tier or a three-tier application development model. In the two-tier model, the data and the stored procedures reside on a SQL Server. The application tier contains the GUI (graphical user interface) as well as the code that interacts with the tables, views, and stored procedures that reside on the SQL Server. In the three-tier model, the data and the stored procedures once again reside on the SQL Server. The difference is that the application tier contains only the GUI. The code that interacts with the data tier is located in a separate library, generally a Visual Basic DLL. We refer to this as the business logic tier. You can then use the business object, or DLL, with multiple applications. The three-tier model promotes reuse, and further separates the application from the data tier.