10 December 2007
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.