1 SP splitted into 12 Sub SPs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

1 SP splitted into 12 Sub SPs

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-12-12 : 10:18:31

I splitted 1 big SP into 12 Sub SPs. Is that something common?

the 1 big SP was too big and confussing.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 10:22:14

No, it is not.
But I suspect there is something bloated with your SP. Unless you mean your STREET ADDRESS alteration SP.

Peter Larsson
Helsingborg, Sweden

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-12-12 : 10:29:30

Unless I'm missing the point here, I've always thought writing modular code is a fairly common practice. If you've one monster procedure that's performing a multitude of different functions, ime there's lots of benefits to making breaking it down into smaller more clearly defined procs...

Mark

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-12-12 : 10:33:59

yes its my street address sp.

its huge. i had to splitted it. i have around 12 sub sp which split individual fields. so now every sub SP Split an individual field.

if its not common. should i leave it as one huge SP?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 10:42:33

No, in your case 12 sub-SP should be enough. I have seen some of the code...

I was refering to the shear number (12) sub-SPs. In my professional life, I sometimes have to call 2, maybe three sub-SP.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-12 : 11:26:25

Also you can have wrapper procedure which exceutes all other procedures

Create procedure BigProcedure
as
EXEC sp1 -- parameters
EXEC sp2 -- parameters
.
.

Madhivanan

Failing to plan is Planning to fail

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-12-12 : 13:18:34

In my pro life. i seen 1 SP calling 5 SPs. And one of the sub SP was longer than the parent one.

My SP dont have Parameters. is it normal?

Kristen
Test

22859 Posts

Posted - 2006-12-12 : 14:12:26

"I've always thought writing modular code is a fairly common practice"

Yeah, me too, but the overhead of calling child-Sprocs seems to go against the grain of "performance". We do do it now-and-again, but certainly not for the convenience of making the code more reasonable - which, OTOH, we do do in our application languages.

Pity really, because it would make the SQL more maintainable.

However, I do think that:


IF @Param1 = 'SEARCH'
BEGIN
    SELECT * FROM MyTable WHERE MyKey = @PARAM2
END
ELSE IF @Param1 = 'UPDATE'
BEGIN
    UPDATE MyTable SET MyCol1 = 'FOO' WHERE MyKey = @PARAM2
END

is better replaced with


IF @Param1 = 'SEARCH'
BEGIN
    EXEC dbo.usp_XXX_Search @SearchKey = @PARAM2
END
ELSE IF @Param1 = 'UPDATE'
BEGIN
    EXEC dbo.usp_XXX_Update @UpdateKey = @PARAM2
END

because the individual steps will have individual query plans cached.

That we do do.

There, that should give the spell checker a fit with the duplicate words!

Kristen

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-12-14 : 16:31:14

i read its not good to used nested stored procedures. Should rolled back to 1 huge stored procedure?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 16:37:11

Where did you read that?

Tara Kizer

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-12-15 : 08:09:36

tkizer,
http://www.sql-server-performance.com/stored_procedures.asp

Avoid nesting stored procedures, although it is perfectly legal to do so. Nesting not only makes debugging more difficult, it makes it much more difficult to identify and resolve performance-related problems. [6.5, 7.0, 2000] Updated 04-03-2006

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-15 : 09:28:27

Again, the answer is, it depends. In some cases it makes sense, in others perhaps not.

Sounds like you may have fallen into the latter case

It's all about the architect and their mind set

In most instances I don't have nesting...in on particular case I have about 24 "sub" sprocs...with 2 wrappers...mostly because each sub sproc is it's own unit of work and can be "restarted" be specifying the "step" when calling the wrapper

I should blog that code

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources