January 17, 2011 at 3:39 am
Which is better approach
1. Multiple SPs for similar functionality or
2. Separate proc for each functionality
let say for performing I/U/D operations on a table ...what is advisable single proc or multiple proc and why?
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
January 17, 2011 at 6:49 am
I prefer a seperate SP for the delete operation. At work we have seperate SP's for Insert/update but my personal preference is just one for the insert/update.
My experience is that when you insert or update data the validation rules tend to stay the same, so I like to make it simpler for myself and the dev calling my SP.
With the new SQL 2008 MERGE statement it might even make more sense to have it as one SP.
January 17, 2011 at 7:33 am
You might want to do a performance test or analyse the query plan, but my feeling is that the multi-proc approach would be better for the optimizer, and would not require much more maintenance than writing one proc.
The only benefit to having one proc as far as I can see is that you would have less procs, which is not really a benefit per se, and may be more difficult to maintain that having one proc per operation. You could write a factory to generate the procs for you if you have a one-one mapping between the base tables/views and your business objects.
January 17, 2011 at 7:49 am
The answer to your question is the almost standard answer to any T-SQL question, that is "It depends".
Before making your decision I strongly suggest that you spend some time reading some excellent articles, published here on SSC, on the subject of EXECUTION PLANS and the effect of cached execution plans on the operation of any T-SQL statement.
January 17, 2011 at 11:47 am
CELKO (1/17/2011)
You were asleep in your freshman Software Engineering class! Coupling? Cohesion? Remember all that stuff?Each module of code should have one entry and one exit point. Each module of code should perform one and only one task. Google "functional cohesion" and software engineering.
What's wrong with the approach of using a single sproc?
The functionality can be described using a simple sentence: "Change the data." 😀
Do you actually use a separate sproc to initialize several variables? Or a single sproc for each SQL statement (since this is the finest granularity of "one and only one task")? In which of your books can I find a "bullet-proof" definition that will be ultimate and won't leave room for any kind of discussion?
January 17, 2011 at 12:23 pm
Each module of code should perform one and only one task.
Do you mean that the MERGE statement in SQL 2008 should NOT exist, because of some one who ranks himself/herself as the guru of all things relational.
January 17, 2011 at 2:16 pm
Joe
Seriously, does anyone study Yourdon, Gane & Sarson, DeMarco, Constantine, Myers, Dijkstra,
Wirth, Manna, Gries, etc. any more?
Isn't your question about Dijkstra, answered by your own words:
They are mostly old carbon paper copies that are a pain to read.
Nobody has the money to clean them up and print them.
Isn't obvious that if they were considered relevant today, someone would put up the money to clean them and print them - even horrors an enterprizing book publisher.
January 17, 2011 at 4:50 pm
CELKO (1/17/2011)
...But thanks to Texas oil money, I can get a dictionary of a dead language :crying:
You're not talking about ANSI92 SQL "standard", are you? :hehe:
Edit: Over here in Europe we don't really care about any ANSI standard, you know...
January 17, 2011 at 10:37 pm
CELKO (1/17/2011)
You were asleep in your freshman Software Engineering class! Coupling? Cohesion? Remember all that stuff?Each module of code should have one entry and one exit point. Each module of code should perform one and only one task. Google "functional cohesion" and software engineering.
Although I certainly agree with functional cohesion, there's absolutely no reason why you can't have multiple exit points depending on the things like error conditions, etc, ad infinitum.
There are also exceptions to functional cohesion in database programming. There's no sense in having a myriad of stored procedures that do one thing well if they're only going to be called upon once.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2011 at 3:57 am
CELKO (1/17/2011)
Coupling? Cohesion? Remember all that stuff?Each module of code should have one entry and one exit point. Each module of code should perform one and only one task. Google "functional cohesion" and software engineering.
I am with LutzM on this, how far do you take the "Each module of code should perform one and only one task" approach. Sometimes we need to be pragmatic about things and do what is practical. Everything has an opportunity cost.
And what about not duplicating code - so if I have the same code in the update,insert and delete proc, I need to change it in 3 places. Bottom line - make an educated decision that works for what you need.
What bothers me is that every time I write a query I am tightly coupled to the table definition it queries:-D
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply