Solved

T-SQL Stored Procedure - Multiple row insertion

Posted on 2003-11-21
8
282 Views
Last Modified: 2008-02-01
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~
0
Comment
Question by:shiukei
  • 5
8 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9798448
You do not need to send comma delimited string, but just send the order number.

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





0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9798804
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
0
 

Author Comment

by:shiukei
ID: 9798884
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?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9798958
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





   
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 150 total points
ID: 9805799
This sample proc below is helpful is parsing ordline nums

drop procedure sp_GetOrdLines
go

Create   Procedure sp_GetOrdLines
(
  @ordlines varchar(8000)  = '111,112,33,445,666,7777,345,45,4545, 4545, 4'
)
as
begin

declare @index int,
        @previndex int,
        @ordline int


If Len(@ordlines) > 0
    Select @ordlines = @ordlines + ','

select @index = charindex(',', @ordlines, 1), @previndex = 0

While @index > 0
begin
 
  select @ordline = convert(int, substring(@ordlines, @previndex + 1, @index - @previndex - 1) )

  -- this your ordline number
  select @ordline
  select @previndex = @index  
 
  select @index = charindex(',', @ordlines, @index + 1)
  If (@index <= 0) break
 
end

end



0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9946480
Would you need further help? If not, why not close the issue? :)

Regards.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now