A few months ago, my colleague Jason Honicker wrote an entry on the EXTOL Technology Blog explaining how database triggers can be used to expand the functionality of your database to do more than just store data. I wanted to further the discussion on databases and talk this time about stored procedures.
A stored procedure is a routine, or program, which resides as part of the database itself. It’s typically written in SQL, PL/SQL, Java or .NET, depending on the type of database being used. Stored procedures enable developers and system administrators to maintain only one piece of code, which may be used across multiple business applications. This takes some of the pressure for business application developers to have to develop the database function directly into the business application. Instead, they simply need to know the procedure name, the parameters it requires and what kind of data could be returned.
By that same token, it’s important to note that while this may be a significant advantage for some, it may be its fatal flaw for others. By having essentially one procedure to rule them all, if frequent changes are needed to the stored procedure, there can be a substantial ripple effect to every application that uses it. Changes may need to be made to unaffected applications to accommodate the changes to the stored procedure.
In addition, stored procedures can also be used to transfer more complex or intensive queries from business applications to the database. For example, if your business application requires a series of complex queries or some manipulation on the returned data, you may design the application to do one query. Based on the result set, loop and query the database again and then perform your data manipulation on the final result set.
In that time, you may have opened and closed at least two separate database connections, and depending on the data, transformed a large amount of data over the network. By using a stored procedure, you open and close one connection, and transfer only the data you want over the wire, reducing overall network utilization.
I certainly don’t proclaim that stored procedures are the silver bullet when integrating with databases. Everyone’s mileage will vary. However a basic understanding of these objects can help improve your business application’s efficiency and reduce maintenance.
If you are a user of the EXTOL Business Integrator, and this post has stirred up some intrigue in using stored procedures in your business logic, or you have existing stored procedures that you may want to implement, keep an eye out for our next major release of the EXTOL Business Integrator, version 2.5, where we are enhancing the support of stored procedures in SQL Actions.