Tuesday, March 09, 2004 1:15 PM szurgot

Disconnect in programming with SqlServer Storerd Procedures

I'm working with a system (that I didn't design) that has a good deal of business logic in the database server. While it makes a lot of sense from a performance perspective, it really makes it hard to program. Several functions update different parts of the same table, and now, in order to inject logic into the database, you have to add it to several stored procedures or (in a last ditch effort) update triggers. It really sucks that you can partition stored procedures, or create Objects to manipulate the database. (I know that Yukon will help somewhat by allowing Stored Procedures written in C# or VB, but it still doesn't seem to address the larger disconnect)

How do others handle this problem? Business logic in code? Naming schema?

Filed under:

Comments

# re: Disconnect in programming with SqlServer Storerd Procedures

Tuesday, March 09, 2004 2:11 PM by Jeff Gonzalez

We do a couple of things at my work to alleviate these types of problems.

For 1, we use a very specific naming structure for table names, view names, stored proc names etc...

For 2, we use CodeSmith, a template based code generator, to generate all of our stored procedures for CRUD functionality.

# re: Disconnect in programming with SqlServer Storerd Procedures

Tuesday, March 09, 2004 2:58 PM by NJ John

Hey Chris, I'd move your logic to your B.O. code wherever possible. I think that's always the best way to go, and just leave the minimal CRUD stuff to the SP.

Here's a good list on SQL from Doug Seven, look at item "H".

http://dotnetjunkies.com/WebLog/DougSeven/archive/2004/02/16/7329.aspx

I found quite a few things on there that made me say, "yup, that's me!". :-)

# Take Outs for 9 March 2004

Wednesday, March 10, 2004 1:43 AM by TrackBack

You've been Taken Out! Thanks for the good post.