- added layer of security that can be placed on the database from
- the data functionality making it easier to manage, document, and maintain
- improved performance
- multiple client applications to have consistent database routines
- to many calls to sql-server
- hard to test
One of the most beneficial reasons to use stored procedures is
the added layer of security that can be placed on the database from the calling application.
If the user account created for the application or web site is
configured with EXECUTE permissions
only then the underlying tables cannot be accessed directly by the user account.
This helps prevent hacking directly into the database tables.
The risk of a hacker using the user account to run a stored procedure
that has been written by you is far safer
than having the user account have full insert, update and delete authority
on the tables directly.
Another advantage to using stored procedures, especially in medium to large scale web sites or applications,
is the data functionality is separated from the application making it easier to manage, document, and maintain.
For example, if an application updates the customer table in ten different places,
there can be a single stored procedure and a standard procedure call from the application for this functionality.
If a change needs to be made to the way a customer record is managed,
then the SQL statements only need to be changed in one place, in the database layer.
In most cases, the application is not affected unless the procedure call requires modification.
Changing the procedure call is also easier, because a standard call is already in place.
Managing the data in the data layer avoids having to keep track of embedded SQL calls
that may be different in each place, whenever a change is required.
Stored procedures provide improved performance because fewer calls need to be sent to the database.
For example, if a stored procedure has four SQL statements in the code,
then there only needs to be a single call to the database
instead of four calls for each individual SQL statement.
Of course there is always a tradeoff.
There is an increased workload on the server side that needs to be taken into account.
Another advantage to using stored procedures allows for multiple client applications written in any language
and running on any platform to have consistent database routines.
Each application uses the same procedures and simply has to embed a standard procedure
call for the language in the calling program.