T-SQL Stored Procedure - Multiple row insertion
Posted on 2003-11-21
Now I am doing some re-write of an existing order processing system, while I am trying to convert some frontend frequently-repeated code into a SQL Server procedure, I am facing the following problem :
It is a fragment of code that use to confirm the order lines, say, there is an order with 10 order lines, whearas only 9 out of 10 order lines will be confirmed.
The logic requires conditionally update some fields in various tables that I would not go into details, what really bother me is how to deal with the multiple order lines transactionally atomic (where the transaction should be all rollbacked or all commit).
Obviously there are 2 methods :
1, I could write a loop in frontend VB code then call the stored procedure 10 times, with 10 set of order number and order line number. It would work, but I realized that there's little difference if I keep the application logic in the frontend, in terms of code maintainability, also there will be an increased number of network traffic.
2, I could create a comma-delimited string on-the-fly and send it to the stored procedure, while may sacified the reusability of the stored procedure.
I am not happy with either method, and I would be grateful if someone could share his/her own experience on this common problem.
My thanks in advance~