Solved

T-SQL Stored Procedure - Multiple row insertion

Posted on 2003-11-21
8
286 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free eBook: Backup on AWS

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

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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