November 17, 2005 at 2:27 pm
This is sort of a 'Best Practices' question, but not too sure where else to ask.
I've got an ASP.NET site that has a form entry that will populate multiple tables
in my database. Tables like -> Project Details, Contact, organization.
Should I write one big sproc to receive all items that will be inserted into all the tables, insert each small piece(create organization entry, get the identity) and then create the main entry(project details, with all the gathered identities)?
--Or--
Should I write a number of small sprocs(create organization, create contact) and handle all the table entries within the ASP.NET page piece by piece?
Any advice is greatly appreciated, or if I'm not very concise, please let me know.
Cheers
-- McWare
November 17, 2005 at 2:43 pm
Small.
For a couple reasons, Isolates code so if there is an error it will be in a single smaller procedure.
Re Use, you can reuse smaller insert/update procedures as singles in different parts of your app without having to collect all the information the 1 huge procedure requires.
Risk, Making changes to single procedures if you have an error most of your app should continue to work, but if small error in huge procedure your app will be dead.
There are other reasons, stored procedures should be written to do single isolated processes, not huge multistatement processes.
November 17, 2005 at 2:46 pm
Great. Sounds like good advice.
Thanks a bunch
-- McWare
November 18, 2005 at 5:15 am
Consider writing many small sprocs which are called from 1 main sproc. This way you still have the flexibility of isolating the individual functions (as mentioned above), but you will only make 1 call from the asp page (better for performance). This can also be considered a business unit of work.
This technique also makes it easier to trap the level 16 errors, since these errors will exit the sproc immediately. If a Level 16 error occurs in one of the small sproc it will be returned form the "Main" calling sproc and you can now control the rollback in the main sproc, or continue processing some of the smaller sprocs.
steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply