Web Development

General discussion


When to use stored procedures vs. SQL

By MaryWeilage Editor ·
This week's .NET e-newsletter helps you determine when to use stored procedures vs. SQL in the code.

Which way do you prefer to approach your applications? Have you encountered problems with one or both scenarios? Let us know by posting to the discussion forum.

If you aren't subscribed to our free .NET e-newsletter, click the following link to automatically sign up:

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Stored Procs VS T-Sql Code

by denouvredb In reply to When to use stored proced ...

In my opinion, I feel that writing stored procedures are very beneficial because the specific database syntax can be optimized at a greater level in stored procedures. Sure you can put this in middle tier code, but the conversion of this Sql code syntax to a language specific acceptable code can be cumbersome(remembering the qoutes etc). Re-use & maintenance at a DB level, sometimes without even changing the middle tier, is a huge advantage of stored procs. My coding life has become easier with the use of stored procs.

Collapse -

sProcs and security

by Grant Maw In reply to When to use stored proced ...

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.

Collapse -

Stored procs simplify app development & Maintenance

by chrismarshall In reply to When to use stored proced ...

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.

Collapse -

by dgallery In reply to Stored procs simplify app ...

In general I prefer stored procedures when working with simple tasks. But when the code gets complex, stored procedures are harder to maintain and unwieldy. The language is clumsy and limited compared to Java and C#. The main benefit I see in placing the SQL in code is that you can create object oriented classes around your data. In a complex application this is important. I recently had an app I tried to do in a SP, but it just wasn't working out. When I converted it to classes in Java all the business rules were far better expressed and easy to maintain. I used a SP to initialize my class objects, then processed and output the data via Java.

Collapse -

stored procedures vs. SQL each has advantages

by frank_picardi In reply to When to use stored proced ...

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.

Collapse -

stored procedures vs. SQL

by jrm213 In reply to When to use stored proced ...

Another thing to note is the maintainability of the software you are developing. If something needs to be changed in a sql statement or command and your application is already compiled and distributed to your clients, each machine will need to be visited in order to update the executable unless their is some type of automated update feature available. If you had used stored procedures you could modify the stored procedure on the server, and the change simply occurs for all client machines.

Collapse -

Business Rules Belong in the Business Tier, NOT the Database

by DamonCarr In reply to When to use stored proced ...

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:


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:


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

Collapse -

Dynamics of business rules

by Urri Oz In reply to Business Rules Belong in ...

Are there other benefits of having business rules in a business tier versus as a stored procs in a database considering there is no plans whatsoever to change from SQL Server to Sybase?

Collapse -

Remember the overall architecture as well as integration

by kb8vme7 In reply to When to use stored proced ...

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.

Collapse -

This goes against the grain

by DamonCarr In reply to Remember the overall arch ...


For integrating with "other business systems or applications down the road" stored procedures are not a good choice. You should look at the global standard called Web Services. This is part of a good SOA architecture. Integrating at the database layer is vendor specific and not a generally good idea.

Web Services are callable from just about anyone, and you can always (if you must) have the implementation of the Web Services call invoke the stored procedure. But the use of Stored Procedures as a mechanism of Application Integration is not a good idea at all unless you are in a very, very small shop or say work for Oracle or one of the DBMS vendors.

My two cents. Web Services exist for a reason. Application Integration cross platform. seamlessly via the Lingua Franca of HTTP/S and XML. OCI for Oracle or TDS for SQL Server are hardly open standards.

In your scenario:

"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."

Again, in a very closed shop you might be able to get away with this. But it is much more likely that you will have a vendor with a completely different database and completely different platform. As architects, we must design for change, scalability, robustness, and a number of other criteria. If I tell a vendor on SQL Server that they have to invoke PL/SQL it would be a nightmare. Again, Web Services exist for a reason.

While plausible, your scenario is not likely, and more importantly, not an accepted standard or practice that would pass the scrutiny of say the top 10% of architects in the country.

Again, as I said in my previous post: BUSINESS RULES BELONG IN THE BUSINESS TIER NOT THE DATABASE. Expose this logic through an SOA via Web Services with the option of a higher performance protocol such as .NET remoting. With a factory pattern (or abstract factory), you can usually support one API and both connectivity strategies.

If you are in the New York area you can attend one of our courses on this subject.

Kind Regards,
Damon Carr, Chief Technologist
agilefactor, inc.

Related Discussions

Related Forums