Microsoft® SQL Server 2008 R2 Unleashed

Advantages of Stored Procedures

Using stored procedures provides many advantages over executing large and complex SQL batches from client applications. Following are some of them:

• Modular programming—Subroutines and functions are often used in ordinary 3GL and 4GL languages (such as C, C++, and Microsoft Visual Basic) to break code into smaller, more manageable pieces. The same advantages are achieved when using stored procedures, with the difference that the stored procedure is stored in SQL Server and can be called by any client application.

• Restricted, function-based access to tables—A user can have permission to execute a stored procedure without having permissions to operate directly on the underlying tables.

• Reduced network traffic— ...