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
