Jeremy D. Miller -- The Shade Tree Developer

Sponsors

The Lounge

Wicked Cool Jobs

Syndication

Recent Posts

Tags

View more

News

Blogs I Read

Archives

Advertisement

Jeremy D. Miller -- The Shade Tree Developer » The Worst Possible Way to use a Stored Procedure
Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
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
Posted Thu, Jun 9 2005 6:01 PM by Jeremy D. Miller
Filed under: ,

[Advertisement]

Comments

pwstevens wrote re: The Worst Possible Way to use a Stored Procedure
on Thu, Aug 18 2005 9:12 PM
This topic is a pet peave of mine; I'm constantly battling with developers to not do this; however there are cases that I can't see how to eliminate the database business logic. Have you found a way around this scenario (note: this is a trivial example since a simple foreign key could solve this problem... but conceptually the problem exists in other areas where a FK wouldn't work):
Rule:
A Customer can't be deleted if he has any orders.

Possible business flow:
1. Object checks to see if any orders exist, none do.
2. Someone else enters order
3. Customer is deleted

Putting the business check in the SPROC would then put the rule in a context of a transaction.

Any way to avoid this (note: see note abou FK above).

Thank you.
Jeremy D. Miller -- The Shade Tree Developer wrote Apoplexy
on Thu, Feb 9 2006 12:23 PM
Like many historically Microsoft development shops we struggle with grossly inappropriate usage...
Jeremy D. Miller -- The Shade Tree Developer wrote Apoplexy
on Thu, Feb 9 2006 12:41 PM
Like many historically Microsoft development shops we struggle with grossly inappropriate usage...
Jeremy D. Miller -- The Shade Tree Developer wrote Why I do not use Stored Procedures
on Thu, May 25 2006 3:47 PM
I promised myself that I wouldn't ever make another post about stored procedures, but Eric's post on...
Slevdi Davoteca wrote re: The Worst Possible Way to use a Stored Procedure
on Mon, Jul 3 2006 4:24 AM
Business Logic? Surely business logic is a combination of two different things:

1 transformation of data (the job of the application)
2 preservation of data integrity according to current business rules (the job of the dbms)

One of the skills of the system designer is to know which things go where. It is always right to get the dbms to preserve data integrity. Applications can do so as well, of course, but should get business rules from a common place.



Gary Farris - Thoughts On Technology and Everything Else wrote Business Logic In Your Stored Procedure
on Wed, Jul 19 2006 9:58 PM
This argument/problem has popped up in my world again.  I find it is easy for someone familiar with...
Scott’s Blog » Stored Procedures vs. Ad-hoc SQL Redux wrote Scott’s Blog » Stored Procedures vs. Ad-hoc SQL Redux
on Tue, Aug 15 2006 3:07 PM
Jayant wrote re: The Worst Possible Way to use a Stored Procedure
on Wed, Oct 18 2006 10:27 AM

Does a stored procedure run slow (In general) than the application code? We have a situation where we have a heavy use of stored procedure and although there is a lot of validation needs to be performed, we take 28 hours to process 60,000 records and this looks way too slow to me.

RogerBlake wrote re: The Worst Possible Way to use a Stored Procedure
on Fri, Mar 16 2007 8:38 AM

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

Using procedural oriented code is totally meaningless in SQL.  SQL is optimized if and only if it is *not* procedural.  Besides set oriented processing is orders of magnitude much more powerful then procedural resource consuming loop holes.

<<The all time dumbest thing to do is to split your business logic between a SPROC and the consuming middle tier code.>>

No.  That's the second dumbest thing. The dumbest thing is to put business logic anywhere else than in the dbms.  What is the business logic anyway etc a set of constraints that bring meaning to data: question is what can best implement constraints: DBMS.  What is better spend time searching tons of mixed up code generated by a confused spaghetti coder or find it a specific centralized place?

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

Then you should review your HR dept qualifications required for people you hire in db design.  That is a competence issue.  If you hire a developper to do dbms don't expect him/her to do a clean job in such area.

RogerBlake wrote re: The Worst Possible Way to use a Stored Procedure
on Fri, Mar 16 2007 8:41 AM

Business Logic should not be handled applicatively (API code, stored procedure) but declaratively through the use of constraints.  TRying to implement business rules applicatively is basically the most unefficient way to do it.

Ivan wrote re: The Worst Possible Way to use a Stored Procedure
on Wed, Apr 2 2008 5:16 PM

And finally, I'm completely confused... I'm looking for information about stored procedures, whether to use them or not... the more I read, the last I know...

And the worst thing is that I have to explain to my development team the reasons of my decisions. But there's no simple choose.

DotNetKicks.com wrote The Worst Possible Way to use a Stored Procedure
on Tue, Feb 17 2009 1:49 PM

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Stored Procedures are EVIL « TheUnical Technologies Blog wrote Stored Procedures are EVIL &laquo; TheUnical Technologies Blog
on Sat, Jul 25 2009 11:22 AM

Pingback from  Stored Procedures are EVIL «  TheUnical Technologies Blog

Twitter Trackbacks for The Worst Possible Way to use a Stored Procedure - Jeremy D. Miller -- The Shade Tree Developer - [codebetter.com] on Topsy.com wrote Twitter Trackbacks for The Worst Possible Way to use a Stored Procedure - Jeremy D. Miller -- The Shade Tree Developer - [codebetter.com] on Topsy.com
on Sun, Sep 20 2009 8:08 AM

Pingback from  Twitter Trackbacks for                 The Worst Possible Way to use a Stored Procedure - Jeremy D. Miller -- The Shade Tree Developer -         [codebetter.com]        on Topsy.com

Nitin Reddy Katkam wrote re: The Worst Possible Way to use a Stored Procedure
on Mon, Sep 21 2009 3:49 PM

On my first job (and my first software project at a software development firm), I had a project manager on the project who insisted that I write the stored procedures, being the DBA on the project. The client shared his opinion.

The development team, headed by a lead developer, kept pushing business logic my way, into the stored procedures. I stepped up and said, "This isn't how code was meant to be written." The project manager called me away and said, "Look, you can write SQL. They can't. Provide a simple enough interface with which they can accomplish all they need to with a single database call."

Stored Procedures are EVIL | TheUnical Technologies Blog wrote Stored Procedures are EVIL | TheUnical Technologies Blog
on Sat, Sep 26 2009 7:31 AM

Pingback from  Stored Procedures are EVIL | TheUnical Technologies Blog

Enumerations and global constants in SQL Server wrote Enumerations and global constants in SQL Server
on Fri, Oct 16 2009 5:22 AM

Pingback from  Enumerations and global constants in SQL Server

Add a Comment

(required)  
(optional)
(required)  
Remember Me?
Devlicio.us