| Author |
Topic |
|
smokinmunky
Starting Member
3 Posts |
 Posted - 2008-07-11 : 12:17:49
|
| Hello all,When I create a database and create stored procedures for it I tend to create a lot of stored procedures to encapsulate their functionality.For example I would probably create the following sp's for a table:-User_Add-User_Edit-User_Get-User_GetAll-User_GetAllAdmin-User_DeleteRecently, a coworker said that there were too many stored procedures in my project and that I should combine them. However, I disagree. Her recommendation was to combine the Add with the Edit stored procedures. And also to combine all of the gets into one stored procedure. Her argument, was that large number of stored procedures would make maintaining the db difficult in the future. My argument to her was that as much functionality should be encapsulated in the different stored procedures to be more OO like. Combining the functionality of an Add and an Update would only make the sp more complicated and introduce another point of failure. And the Gets in the above example all return different data. Having one Get that accepts a bunch of variables seems like it would over complicate things. So what do you think? Are too many stored procedure a bad thing or should should I create as few stored procedures as possible and combine their functionality? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-07-11 : 12:34:28
|
| think of your sprocs as a database interface to the outside world.in oo an object should communicate with the outside world only through it's interface.then there's the "keep it simple" principle.too many sprocs aren't a problem with good naming conventions and schema use._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
|
LTack
Posting Yak Master
193 Posts |
Posted - 2008-07-11 : 12:42:54
|
| Thanks for this...it helps clear up some thoughts I've had on the issue as well. A fellow programmer and I have gone back and forth on our coding standards...we decided to agree to disagree...but I feel more justified now ;)__________SQL Newbie |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-07-11 : 12:49:23
|
well i have only one coding standard rule for sprocs:always prefix your sprocs with "sp_"  _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
smokinmunky
Starting Member
3 Posts |
Posted - 2008-07-11 : 13:11:05
|
| why is there a need prefix with sp_? In application development Hungarian Notation is on the way out, so why is there still a need for it in the database world? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-07-11 : 13:12:32
|
| no, no, i was joking. whatever you do never use a "sp_" prefix for your stored rocedures._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2008-07-11 : 13:14:08
|
| Don not use sp_, the database will look at the system stored procedures first and then the user defined ones, I use stp_"God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
|
|
|
smokinmunky
Starting Member
3 Posts |
Posted - 2008-07-11 : 13:17:09
|
| I'm talking about stored procedure prefixing in general, is it really needed? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-07-11 : 13:21:11
|
| no it's not needed. but it's nice to have because that way you can differentiate your objects quicker._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
|
OO Principles in SP Design