When to use stored procedures vs. SQL

Stored procedures are also good at helping you avoid SQL-injection attacks. This can be done using inline SQL but it takes more effort.

In these days of heightened security-awareness I find it somewhat surprising that the author did not include at least a brief note about this in the article.

This appears to be a disturbing trend on builder.com these days - articles seem to get rushed out and often seem incomplete. Maybe I'm being too critical, but the standard appears to me to be slipping.

Understanding the database construction takes time and developers new to an organisation can bring the servers to their knees with inefficient sql calls in their apps. Let the few developers that understand the data and sql write the stored procs for everybody. Ongoing maintenance is reduced by using stored procs as a stored proc only needs to be changed once while code to do the same function may be in many applications that all have to be found, changed, and tested.
Tests can be easily performed on a single stored proc using built in measurements simplifying their tests.

I have experience with both stored procedures and dynamic SQL in applications. Your article is pretty much on track. The only thing I would add is if the SQL code is minimal then applying it in the application is reasonable. However, it is usually more effecient to apply complex SQL code into stored procedures and let the SQL Server do more of the work, particularly if performance, concurrent user access, and effeciency are a requirement of the application. The issue of dynamic SQL is no longer as big of an issue in a store procedure for SQL Server 2000 even though it will not cache the execution plan, off loading to the store procedure the bulk of the work will share the process effeciency of the application will reducing the bandwidth requirement of transmitting large dynamic SQL code.

One approach I have taken at times is to apply a hybred solution in the same application; in which if the SQL is pretty static including parameter definitions then a stored procedure is coded. Usually, when retreiving recordsets which require dynamic filtering, where a "WHERE" clause is built dynamically, constructing T-SQL statements in the application is more adventages.

It is well established that N-Tier architectures work, especially as a facilitator for all the Service Oriented Architecture Hype (which is well deserved in my opinion). Putting business rules in the database is a bad idea in general (if I had to give a binary response). In fact putting any business rules, except for those that you must for performance reasons in the database is a bad idea (and then I would really want to look at that rule and see why). What makes me take this position after 14 years building global multi-million dollar systems?

Well today, you may confidently think you will always use SQL Server at your shop or even your enterprise. However, some day when you are asked by your new CTO who happens to play golf with the CEO of Sybase to migrate to Sybase on Solaris (the new CTO hates Windows), do you want to spend months changing your code or days? And assuming .NET again if you make the mistake of putting the actual client namespace in your code you will have to change all of those references.. Ugly.

I recently participated on a groundbreaking architectural implementation of an abstract factory pattern for relational databases for .NET. Good software engineering dictates we minimize dependencies and only have them as much as possible to Interfaces and Abstract Classes. Well imaging your database using a classed called (we are in the .NET environment) AdoHelper. This class would have all the database operations you would expect for DataSets, DataReaders, Transactions, etc.

To see this visually go here:

http://www.agilefactor.com/daab3/

Click on the bottom left on ?Class Diagram? to see the high level structure.

AdoHelper is completely abstracted from the underlying concrete child implementations (and it has no knowledge of them as we use reflection to load assemblies ? critical for a good factory implementation). When a database is needed it is retrieved through a 'factory' method that uses reflection to instantiate the SQL Server, Oracle, DB2, or whatever assembly, load it and create an instance. It then sends it back, cast into the base abstract class.

Look at the same URL but click on the ?DAAB3 Sequence of Creation? for a sequence diagram of how this works.

Your entire application (and hopefully in an N-Tiered environment only your business tier is speaking to your Database Tier) it would only have a dependency (think UML Dependency) on the AdoHelper. Not one class in your application has any knowledge of the children of AdoHelper so you are free to support any database you like.

This is exactly what we accomplished. I have used this on many projects with great success, and my firm even offers a class on this technology (and helps companies implement it all over the world). If you write ANSI Standard SQL and keep stored procedures to a minimum, you can have an application that is fairly database Agnostic. A change from SQL Server to Oracle could be done in days.

But what about feature X? Or Feature Y that are database specific?

This environment is meant to be extended. A good example would be retrieving data as XML. Most major vendors are doing this but not all. You can still have the same method name, yet in the implementation serialize the DataSet (or DataTable or DataReader) to XML. The caller doesn't know that the database didn't do it natively.

For example, the SQL Server concrete class does exactly that. It sends back SQL using the SQL Syntax SQL Server supports for retrieving XML. But what about an older version of Sybase? No problem. You can implement the same functionality, only this time returning the DataSet as XML.

So where can you find this framework? Go here:

http://www.gotdotnet.com/Community/Workspaces/Workspace.aspx?id=c20d12b0-af52-402b-9b7c-aaeb21d1f431

There are equivalent environments for J2EE developers. Speaking as a software engineering leader who has to write products that are database agnostic, anything but the most trivial of applications can benefit from a framework such as this. And I do not think Stored Procedures are bad. I just HATE multiple code bases and every time we have one and we add a feature, we have to maintain a version for all the major vendors and it is a real pain.

Also..In many cases just use prepared statements! You gain much of the benefit and they are one of the most underutilized techniques I can think of (that and doing decent object-oriented design). Sorry I couldn?t resist.. Read the Standish group statistics, The custom software industry is in incredibly horrible shape and my firm and many like mine are out to fix it.

Kind Regards,
Damon Carr, Chief Technologist and CEO
agilefactor
http://www.agilefactor.com/

It might be helpful to remember that placing your business logic in stored procedures will help with integration with other business systems or applications down the road. For example, say I place the logic for some type of Order Entry processing across a set of stored procedures tied to the database.

Later, my manager comes to me and says that company X wants to use their application/system Y to integrate with ours. As part of this integration, they want to remotely submit orders. More specifically, they want to submit orders directly from their system and not use any of the middleware interfaces that already exist. If the logic for Order Entry has been implemented as stored procedures, then calling these SPs is certainly an option for them. If the SQL code was embedded behind our own application specific layer, then re-use could far more difficult.

I did a web project with all le statement in the code where it did well for simple statements but with complexe statement for update or insert where performence was important i we had some problems.

I also did another web project where we put all statement in store proc. It was ok until we had over 100 store proc and we had to change some part of the project. So if i had a store proc that select all active employees and i had to change it to all active employees that are available then i was'nt sure if i had to create another one or change this one. if i change it, does another part of the project be affected.

I know we add a documentation problem but even so if you don't want to many store proc that are not used it become complexe to manage.

So the next project will be the simple statement like (select * from table where field='value') in the code but the big complexe statement will be in store procedure.