Solved

T-SQL Stored Procedure - Multiple row insertion

Posted on 2003-11-21
8
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

732 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