shiukei
asked on
T-SQL Stored Procedure - Multiple row insertion
Hi everybody,
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~
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~
Actually this should read as follows (see modifications made with SET ROWCOUNT 1)
declare @temptable table(ordlinenum int null),
@vRowCount int,
@i int ,
@vOrdLineNum int
insert into @temptable
select ordLineNum
from Order
where OrderId = @orderid -- input parameter for sp
select @vRowCount = @@ROWCOUNT
select @i = 1
WHILE (@i <= @vRowCount)
BEGIN
-- Get each ordlinenum
SET ROWCOUNT 1
select @vOrdLineNum = OrdLineNum
from @@temptable
delete from @temptable
SET ROWCOUNT 0
Begin Tran
-- Process your order line here
Commit Tran
select @i = @i +1
END
HTH
Namasi
declare @temptable table(ordlinenum int null),
@vRowCount int,
@i int ,
@vOrdLineNum int
insert into @temptable
select ordLineNum
from Order
where OrderId = @orderid -- input parameter for sp
select @vRowCount = @@ROWCOUNT
select @i = 1
WHILE (@i <= @vRowCount)
BEGIN
-- Get each ordlinenum
SET ROWCOUNT 1
select @vOrdLineNum = OrdLineNum
from @@temptable
delete from @temptable
SET ROWCOUNT 0
Begin Tran
-- Process your order line here
Commit Tran
select @i = @i +1
END
HTH
Namasi
ASKER
Hi Namasi
as the orderline is select by the user in the listbox, how about if the orderline confirmed is not in sequence, say 1,3,4,5,7,9 out of the total of 10 order line?
as the orderline is select by the user in the listbox, how about if the orderline confirmed is not in sequence, say 1,3,4,5,7,9 out of the total of 10 order line?
OK. Then what you can do is add selected ordlinenumbers into a permanent temptable from VB.
TmpUserOrdLine
columns
1) spid int not null
2) ordlinenum
Then use this temptable within your proc. You will then need to pass sp_id and orderid as input parameters to the proc.
spid is obtained using
select @@spid as sp_id
select ordlinenum
from TmpUserOrdLine
where sp_id = @sp_id
Or as you said earlier you can pass ordlinenums as concatenated comma delimited string of datatype varchar(8000). 8000 chars is the max you can get as a varchar datatype.
Then parse this string to obtain ordlinenumbers. See charindex function. This function and substring function can be very useful to parse strings.
Then process each ordlinenum as shown above with individual begin and commit trans if you do not wish the atomic behavior.
HTH
TmpUserOrdLine
columns
1) spid int not null
2) ordlinenum
Then use this temptable within your proc. You will then need to pass sp_id and orderid as input parameters to the proc.
spid is obtained using
select @@spid as sp_id
select ordlinenum
from TmpUserOrdLine
where sp_id = @sp_id
Or as you said earlier you can pass ordlinenums as concatenated comma delimited string of datatype varchar(8000). 8000 chars is the max you can get as a varchar datatype.
Then parse this string to obtain ordlinenumbers. See charindex function. This function and substring function can be very useful to parse strings.
Then process each ordlinenum as shown above with individual begin and commit trans if you do not wish the atomic behavior.
HTH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Would you need further help? If not, why not close the issue? :)
Regards.
Regards.
Then in stored proc
declare @temptable table(ordlinenum int null),
@vRowCount int,
@i int
insert into @temptable
select ordLineNum
from Order
where OrderId = @orderid -- input parameter for sp
select @vRowCount = @@ROWCOUNT
select @i = 1
WHILE (@i <= @vRowCount)
BEGIN
Begin Tran
-- Process your order line here
Commit Tran
select @i = @i +1
END
HTH