Most
DBAs are aware of the many benefits of using stored
procedures for data access; in short, performance and security. By using stored
procedures we can offload some to all of the business rule enforcement that is
not handled by constraints.
Database servers tend to bottleneck more on I/O than memory or CPU. The
hardware backing a database server tends to be stronger than an application or
web server. Using some of those free resources can ease the burden on your
application servers while reducing the amount of network traffic and database
transactions. Why would you want to worry about in-line SQL and ad hoc queries?
Maintenance, permissions and tuning are often complicated by using such
primitive methodologies. Stored procedures are beautiful things.
By using one procedure for inserts and updates, you can minimize the types of
calls to a database, simplifying the developer’s job. If you are using
surrogate keys, it is very simple to implement:
create procedure Client_Add(iClient_UID, iClientName, iDateContract)
if(iClient_UID = 0 or iClient_UID is null) then
insert into
Client(
ClientName,
DateContract
)
values(
iName,
iDateContract
);
else
update
Client
set
ClientName = iClientName,
DateContract = iDateContract
where
Client_UID = iClient_UID;
end if;
If you are not
using surrogate keys, you need to check if the record exists based on the
primary key (not a bad thing to do even if you are using surrogate keys).
Tables with foreign keys can be translated and/or validated. In many cases, we
can get away from costly triggers by using stored procedures to handle
translations. Validate your foreign keys
Similarly for the retrieves, you can use the surrogate
key to determine whether to grab a specific row or some to all rows.
While procedures facilitate dynamic SQL beware that they introduce similar
risks as inline SQL. Always validate your inputs.