Advantages of Stored Procedures

Using stored procedures provides many advantages over executing large and complex SQL
batches from client applications. The 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—Someone can have access to execute a
stored procedure without having permissions to operate directly on the underlying
tables.
. Reduced network traffic—Stored procedures can consist of many individual SQL
statements but can be executed with a single statement. This allows you to reduce
the number and size of calls from the client to the server.
. Faster execution—Stored procedures’ query plans are kept in memory after the first
execution. The code doesn’t have to be reparsed and reoptimized on subsequent
executions.
. Enforced consistency—If users modify data only through stored procedures, problems
that often result from ad hoc modifications (such as omitting a crucial WHERE
clause) are eliminated.
. Reduced operator and programmer errors—Because less information is being
passed, complex tasks can be executed more easily, with less likelihood of
SQL errors.
. Automating complex or sensitive transactions—If all modifications of certain
tables take place in stored procedures, you can guarantee the data integrity on those
tables.

---------------------------------------------------------------------------------

Stored procedures are compiled and are the fastest possible
means of executing a batch or query.

Executing the processing at the server instead of the desktop
greatly reduces network traffic.

Stored procedures offer modularity and are an easy means of deploying features and
code changes. If the front-end application calls a stored procedure to perform some
processing, modifying a stored procedure in a single location upgrades all users.

Stored procedures can be an important component in database security. If all user
access goes through stored procedures, direct access to the tables can be denied and
all access to the data can be controlled.