Multiple transactions in an stored procedure

[batteam]

A couple of good SQL books that I am reading say that its good practice to design stored procedures to accomplish a single task. However, I see many examples (in the books) where an sp has multiple transactions, specifically, two or more inserts in a single sp. Question: is it OK to put two or even three inserts (into different tables) into the same sp? Is that considered a 'single task'? Seems to me it would be quicker and more efficient for the server. Or is it advisable to only include one Insert/Update/Delete per sp?

Thanks for any advice you can offer.

 

[jdgonzalez]

Personally, I would suggest using a single sp to perform a teak. Doing it this way allows you to treat the task as a single transaction even though there may be multiple inserts, updates, etc taking place. If insert fails within the sp, you can then rollback the transaction to maintain data integrity.

To answer your question about a single task, that really depends on how you view a task. One could define a task as entering a new problem into trouble ticket db, but that task may involve several sub tasks depending how your business rules and database design or it could just mean insert a new row into a table.

 

[mrdenny]

I follow the one process per procedure. The ticketing system is a good example. To Add a ticket to the system is a procedure. That procedure calls other procedures to perform functions that have to be done more than once. To update a ticket is another procedure. To add notes is a third procedure (the add notes procedure is called by the add ticket and update ticket procedures.

This was if there is an error somewhere in the add ticket procedure down towards the bottom, I can roll the entire process back and fail it out without having to go write clean up code.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[batteam]

Thanks jdgonzales and mrdenny.

Sounds like defining a 'task' can be a little subjective. I'll have to give this a little more thought, but right now I'm leaning toward using two inserts in one sp - in my case, adding a client appt. record to the appt. table and adding that same record to an appt. archive table. In this case, all the parameters I send from my Project (to the sp) are exactly the same, so it would seem to make sense to use the one sp. A person could consider this one task: adding an appointment. I agree that updates, deletes etc., of any appt. record would be another process/task.

Thanks again for your time.