Monday, June 13, 2005

The Worst Possible Way to use a Stored Procedure

Rant time...

Repeat after me please,

I will not put any business logic into a stored procedure.
I will not put any business logic into a stored procedure.
I will not put any business logic into a stored procedure.
I will not put any business logic into a stored procedure.
I will not put any business logic into a stored procedure.

Okay, if your business logic can be expressed easiest through a declarative SQL WHERE clause, a SPROC can be a cool way to go. Set-based logic is almost always simpler to do with SQL than mucking through with procedural C#, but that's a different rant for another day, and I'm definitely talking about procedural code within sproc's here.

Question: How do I know if I might be doing something in a sproc that I shouldn't be doing? Answer: If there is *anything* other than CRUD in your sproc (IF/ELSE/ENDIF, LOOP/END LOOP, etc.).

The all time dumbest thing to do is to split your business logic between a SPROC and the consuming middle tier code. Twice now in the past 12 months I've bumped into cases where business logic is performed inside a sproc, then interpreted by middle tier C# or VB6 code. Splitting the logic into multiple spots like this makes a system harder to understand. It also makes a system brittle to change because some logic is duplicated in both places. A change in the sproc or C# can, and usually does, break the other. This is a case of the Don't Repeat Yourself principle the Pragmatic Programmer guys talk about.

I had an interesting experience as a consultant one time. I was fresh into the project working with some legacy C# code (yes, there is already legacy .NET code). I had just stumbled on the fact that most of the business logic was really in T-SQL procedures. In many cases, the C# code undid some of the T-SQL transformations to filter the results further. The client architect was giving us a bit of a tongue-lashing ("you consultants better not write crappy code, and there better be tests for everything you write") while I was looking at a 5,000 line stored procedure, with his name all over the comments, trying to decide if some erroneous data was coming out of the database or being transformed in the C# code. This particular system is the subject of a case study/testimonial on MSDN as an example of all the wonderful things about .NET;)

"Thank you for listening, I feel better now." -- Roy Moore


Anonymous Anonymous said...

I understand your argument, but the idea of n-tiered applications in which no business logic resides in the backend is a badly flawed and expensive model. Long before the idea of "N tiers" (and even "Client-Server") developers were exclusively implementing most, if not all of their business logic in the backend, and quite successfully. Many of these "host-based" apps are still around today.

With today's more modern technology, it is surprising how simple and straightforward an application becomes when 100% of the business logic resides in the database. Yes, I know that SQL-Server is not as efficient at carrying out certain tasks (like string manipulation) as other technologies. I am also familiar with the assertions that looping and cursors are evil, bla bla bla. I do not advocate that entire apps should be written in some top-down way in the database either, but putting business logic in the database is NOT at all evil.

Ultimately, the NET gains realized from object oriented N-Tiered applications are really MINISCULE when compared with what really amounts to "Host-based" or pure "client-server" architectures.

All business logic in the backend eliminates the possibility that changes to the logic might require changes to other tiers, written in different technologies. All business logic in the backend does not require expensive object-oriented developers. The host-based model also brings with it a code base that is far, far, far easier to maintain and change. Finally, since most all of the work is done in the backend, the costs normally associated with lots of expensive servers and desktops is eliminated.

Now, with the recent advent of calling into .Net Assemblies directly from transact SQL, many of the "N-tiered gains" go away too! Rather than loop in my transact SQL sproc, I can implement the logic in a C# method.

Sorry, but I completely disagree with you. Just about every successful app I have ever seen that is X86 based in some way involves a straight-forward presentation layer with all of the real work (including so-called "business logic") occurring in the database. Whenever they have involved so-called "middle-tiers," then the middle-tiers are nothing more than wrappers around stored procs that move data back and forth, but otherwise do nothing (yipee skipee--we can say we have a "middle-tier," woooo-who).

Every app that I have ever seen that has some convoluted object-oriented mess that attempts to implement business logic and / or abstract the database in some "cool" object oriented way has gone down in flames and has serious performance problems.

Now, I feel better.

2:28 PM, November 17, 2005  
Anonymous Anonymous said...

Nice site!
[url=]My homepage[/url] | [url=]Cool site[/url]

12:50 PM, September 15, 2006  
Anonymous Anonymous said...

Good design!
My homepage | Please visit

12:53 PM, September 15, 2006  
Anonymous Anonymous said...

Good design! |

12:53 PM, September 15, 2006  

Post a Comment

<< Home