| Yes u can do this thing using xml, first u create a xml for multiple record in vb.net and pass it as a parameter to stored procedure and run
sp_xml_preparedocument
in sp for insert every record in xml.
ALTER PROCEDURE [dbo].[insertInvoiceReceive]
(
@strXML nText,
@Result int output
)
AS
Begin
Declare @intPointer int--xml pointer
Declare @InvoiceId int
Declare @GLACID int
Declare @SubGLACID int
Declare @Amount numeric(19,2)
Declare @ItemId int
Declare @POLineItemReceiveId int
DECLARE @IsLocalAdded VARCHAR(1)
DECLARE @INGLId INT
SET @IsLocalAdded = ''
SET @INGLId = 0
set @Result=0
Create Table #tmpTblGL
(
tId int Identity(1,1)
,InvoiceID int
,GLACID int
,SubGLACID int
,Amount numeric(19,2)
,ItemId int
,POLineItemReceiveId int
,IsLocalAdded VARCHAR(1)
,InGLId INT
)
EXEC sp_xml_preparedocument @intPointer OUTPUT, @strXML
print 'start'
insert into #tmpTblGL
SELECT
InvoiceID
,GLACID
,subGLACID
,Amount
,ItemId
,POLineItemReceiveId
,IsLocalAdded
,InGLId
FROM OpenXML(@intPointer,'Table/Data',2)
WITH
(
InvoiceId int
,GLACID int
,subGLACID int
,Amount numeric(19,2)
,ItemId int
,POLineItemReceiveID int
,IsLocalAdded VARCHAR(1)
,INGLID INT
)
EXEC sp_xml_removedocument @intPointer
select * from #tmpTblGL
Declare @Tcount int
Declare @loop int
set @loop=1
--set @loop=@loop + 1
select @Tcount=count(1) from #tmpTblGL
print @Tcount
--print @loop
While(@loop<=@Tcount)
begin
select
@InvoiceId =InvoiceId
,@GLACID=GLACID
,@SubGLACID=SubGLACID
,@Amount=Amount
,@ItemId=ItemId
,@POLineItemReceiveId=POLineItemReceiveId
,@IsLocalAdded = IsLocalAdded
,@INGLId = InGLId
from #tmpTblGL where tId=@loop
if (@POLineItemReceiveId=0)
begin
--Insert
PRINT 'INSERT'
IF(LTRIM(RTRIM(@IsLocalAdded)) = 'Y')
BEGIN
IF EXISTS(Select InGLID From invInvoiceGLlineitem Where InvoiceID = @InvoiceId AND InGLID = @INGLId AND LTRIM(RTRIM(IsAddedGLForPOInvoice)) = 'Y')
Begin
print 'This is B-A'
Update invInvoiceGLlineitem
SET GLACId = @GLACID,
SubGLACId = @SubGLACID,
Amount = @Amount
Where InvoiceID = @InvoiceId AND InGLID = @INGLId AND LTRIM(RTRIM(IsAddedGLForPOInvoice)) = 'Y'
End
ELSE
Begin
print 'This is B-B'
insert into invInvoiceGLlineitem(InvoiceId,GLACID,subGLACID,Amount,ItemId,POLineItemReceiveId,IsAddedGLForPOInvoice)
values(@InvoiceId,@GLACID,@SubGLACID,@Amount,@ItemId,@POLineItemReceiveId, @IsLocalAdded)
End
END
ELSE
BEGIN
print 'This is d'
insert into invInvoiceGLlineitem(InvoiceId,GLACID,subGLACID,Amount,ItemId,POLineItemReceiveId,IsAddedGLForPOInvoice)
values(@InvoiceId,@GLACID,@SubGLACID,@Amount,@ItemId,@POLineItemReceiveId, @IsLocalAdded)
END
select @result=@@Identity from invInvoiceGLlineitem
end
ELSE
Begin
print 'This is A'
print @POLineItemReceiveId
print @Amount
update invInvoiceGLlineitem
--set InvoiceId=@InvoiceId --COMMENET ON 21-SEP-07
SET GLACID=@GLACID
,SubGLACID=@SubGLACID
,Amount=@Amount
,ItemId=@ItemId
where POLineItemReceiveId=@POLineItemReceiveId
set @result=@InvoiceId
end
set @loop=@loop+1
end
drop table #tmpTblGL
Declare @TotalAmount numeric(19,2)
select @TotalAmount=sum(Amount) from invInvoiceGLLineItem where InvoiceId=@InvoiceId
update invInvoiceManager set Amount=@TotalAmount where InvoiceId=@InvoiceId
End |