Tuesday, November 10, 2009

Inserting Multiple Row in database in one time


alter PROCEDURE  [dbo].[p_InsertDetail]
(
@Combined nvarchar(200)
)
as
begin try
begin tran
Declare @ii int,@jj int,@AllId nvarchar(max)
Declare @iItemId int,@Item varchar(200),@Quantity int,@UnitPrice float,@Total float
set @ii=-1
set @jj=-1
while(@ii<>len(@Combined) and len(@Combined)<>0)
Begin
set @AllId=substring(@Combined,@ii+1,CharIndex('/',@Combined,@ii+1)-@ii-1)
set @ii=CharIndex('/',@Combined,@ii+1)
while(@jj<>len(@AllId) and len(@AllId)<>0)
Begin
set @iItemId=convert(int,substring(@AllId,@jj+1,charIndex(',',@AllId,@jj+1)-@jj-1))
Set @jj=CharIndex(',',@AllId,@jj+1)
set @Item=substring(@AllId,@jj+1,charIndex(',',@AllId,@jj+1)-@jj-1)
Set @jj=CharIndex(',',@AllId,@jj+1)
set @Quantity=convert(int,substring(@AllId,@jj+1,charIndex(',',@AllId,@jj+1)-@jj-1))
Set @jj=CharIndex(',',@AllId,@jj+1)
set @UnitPrice=convert(float,substring(@AllId,@jj+1,charIndex(',',@AllId,@jj+1)-@jj-1))
Set @jj=CharIndex(',',@AllId,@jj+1)
set @Total=convert(float,substring(@AllId,@jj+1,charIndex(',',@AllId,@jj+1)-@jj-1))
Set @jj=CharIndex(',',@AllId,@jj+1)
   Insert into t_ItemDetail(ItemId,Item,Quantity,UnitPrice,Total)
   values(@iItemId,@Item,@Quantity,@UnitPrice,@Total)

End      
Set @jj=-1
End
commit tran
end try
begin catch
rollback tran
end catch



---- 1,Item1,5,10.0,50.0,/2,Item2,2,10.30,20.60,/3,Item3,5,11,55,/4,Item4,6,20,120,/

No comments:

Post a Comment