alter Procedure spcInventory
(
@InventoryXML text,
@WarrantID VARCHAR(50),
@Result INT OUTPUT ,
@Addressid varchar(50)
)
as
begin
SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
DECLARE @xml xml
set @xml=cast(@InventoryXML as xml)
begin transaction Inventory
declare @tempInventory table(
Quantity VARCHAR(100),
Description VARCHAR(200),
MakeModel VARCHAR(200),
Valuation VARCHAR(100)
)
insert into @tempInventory(Quantity, Description,MakeModel,Valuation)
SELECT T.c.value('./Quantity[1]','varchar(100)'),
T.c.value('./Description[1]','varchar(200)'),
T.c.value('./MakeModel[1]','varchar(200)'),
T.c.value('./Valuation[1]','VARCHAR(100)')
FROM @xml.nodes ('/Details/Inventory') T(c)
DELETE FROM Report_Inventry WHERE upper(WarrantID) = upper(@WarrantID) and Addressid=@Addressid
INSERT INTO Report_Inventry (Addressid,WarrantID,Quantity,Description_goods,MakeModel,Valuation)
SELECT @Addressid, upper(@WarrantID), Quantity, Description,MakeModel,Valuation FROM @tempInventory
if @@Error<>0
begin
set @Result = 0 --Error
rollback transaction Inventory
return
end
set @Result = 1 --Success
Commit transaction Inventory
End