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 |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2006-05-10 : 10:06:24
|
| Hey allI have recently been playing with transactions, and have a few questions regarding best practices. Here is my scenario:We have a table that stores the contents of a customers shopping cart as xml. This is then committed as SQL Data as orders when the cusotmer proceeds to checkout. I have created 3 sprocs:dbo.usp_InsertOrder - inserts an order (date, customerid, returns @orderid) into an order tabledbo.usp_InsertOrderItem - inserts items relating to an order (itemid, orderid) into an OrderItem tabledbo.usp_DeleteBasket - deletes the order items contained in a basket tableAll these sprocs are executed in a third sproc called:dbo.usp_InsertBasketToOrder - (accepts @customerID) and executes all the above 3 sprocs.FIrst of all, it checks to see if the customer actually has any items in the basket. If so, the first two sprocs are executed within a transaction ensuring that an order is created that has referencing items.The third sproc deletes the basket.What I want to know is, am I complicating things by creating seperate sprocs? I prefer to keep delete, insert, select, etc statements in their own sprocs, but it is necessary for me to encase the logic for two different inserts into 2 seperate sprocs?What are the benefits of my approach and what are the pit falls?All your opinions and arguments will be greatly appreciated!Hearty head pats |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-10 : 11:46:46
|
| Sounds a bit like you might be encapsulating table access in stored procedures rather than business entity access but apart from that it's not a problem.Be aware that an error will not necessarity abort a transaction. With v2005 you can put a try catch in the outermost SP, in v2000 you have to check the error code after every statement.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
raclede
Posting Yak Master
180 Posts |
Posted - 2006-05-11 : 01:06:32
|
| If its just a one liner code per process you could just put all codes in one SP so that its muchsimplier . |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-11 : 01:51:55
|
| only if you intend to always call them in one sp or you might end up with more than one delete statements doing the same thing in separate sprocs--------------------keeping it simple... |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2006-05-11 : 11:45:14
|
Thanks for all your suggestions. It really helps to get other peoples opinions, as sometimes, I get a bit carried away with one method. But from what you all say, it depends....but the general consensus is to keep it simple....but avoid redundancy. So I suppose the best way is to keep it within current requirements, but if another requirement pops up that results in redundancy, readdress the problem!! Hearty head pats |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-11 : 12:05:59
|
| as in"It's never too late for a rewrite"==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Transactions - best practices