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.
| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2009-09-10 : 11:41:04
|
| Hi All -I am wondering if this makes sense from a performance, scalability and general maintenance.I know I can call a stored procedure from another one. However, I have a case where I am considering having one stored proc (the parent) calling many other stored procs. The situation is that the database has tables like: - Person - PersonAddress (one person can have many addresses) - PersonEmail (one person can have many email addresses)...and a few other PersonNNNN tables.The application UI allows the end user to add/update a person and the can add just the basic person attributes (in the Person table) or they can add/update Address or Email.So, I am thinking that AddPerson stored proc would have calls to AddPersonAddress, AddPersonEmail, etc. That way, there would be a single call to the database and not so much back-and-forth network traffic.But I also think that it might be better to keep each call to the database as a discrete unit...because the user can simply update a person's email address and not touch the other person attributes.Anyway, any thoughts about this would be appreciated.Thanks, - will |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-13 : 13:48:22
|
| isnt it better to wrap all these logic in a single procedure and call it AddPersonDetails? it will have parameters to pass values for all the person detail fields. |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2009-09-14 : 13:14:37
|
| I have a similar senario, whereby i call child SP and functions. Because multiple sp use the same logic e.gWe have an sms billing platform called sp_send_smsWe have a certain logic that adds scheduled jobs, if the number of messages exceed a certain amount, incapsulated into another SP. Reason we did this, is because multiple applications that require different logic call this same logic. E.g. we have small batches, personalized batches, bulk batches etcOnly downside is when you have to debug, the error table only gives an error line, which woudl not be specific to a certain sp.Hope this makes sense. |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-09-20 : 21:00:12
|
| Thanks for both responses.I am still working out the solution. Not sure of the best approach and I think that I'll need to spend some time testing to see which approach works the best.Thanks. |
|
|
|
|
|
|
|
|
Parent Stored Proc calling many children procs?