Solved

T-SQL Stored Procedure - Multiple row insertion

Posted on 2003-11-21
8
281 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

14 Experts available now in Live!

Get 1:1 Help Now