USE [QuanLi]
GO
/****** Object: StoredProcedure [dbo].[BuyCar] Script Date: 11/12/2016 08:25:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[BuyCar] --创建存储过程ByCar
(
@Purchase_State varchar(200), --单据状态
@Purchase_Remark varchar(200), --备注
@Supplier_Id int, --外键供应商编码
@WareHose_Id int, --外键仓库编码
@Employee_Id int, --经办人
@xml xml --xml数据流
)
AS
Begin tran t12 --事务名称
insert into Purchase(Purchase_curom,Purchase_date,Purchase_State,Purchase_Remark,Supplier_Id,WareHose_Id,Employee_Id)
values(CONVERT(INT,GETDATE(),112),GETDATE(),@Purchase_State,@Purchase_Remark,@Supplier_Id,@WareHose_Id,@Employee_Id)
declare @id int,@pointer int --定义参数
set @id=@@IDENTITY;---获取自增字段
create table #oo ---创建临时表
(
Purchase_Id int, --采购主表编号（外键）
Product_Id int, --商品编号（外键）
PurchaseMate_Num int, --采购数量
PurchaseMate_Price decimal, --商品单价价格
PurchaseMate_Sum decimal --商品总价

)
execute sp_xml_preparedocument @pointer output,@xml--sp_xml_preparedocument语句 --将xml类型的数据循环添加到临时表
insert into #oo(Product_Id,PurchaseMate_Num,PurchaseMate_Price,PurchaseMate_Sum)
select Product_Id,PurchaseMate_Num,PurchaseMate_Price,PurchaseMate_Sum from openxml (@pointer,‘/xml/product‘)--系统程序sp_xml_preparedocument指向‘/xml/product’
with(

Product_Id int, --商品编号
PurchaseMate_Num int, --采购数量
PurchaseMate_Price decimal, --商品单价价格
PurchaseMate_Sum decimal --商品总价
)
update #oo set Purchase_Id=@id --修改值
insert into PurchaseMate(Purchase_Id,Product_Id,PurchaseMate_Num,PurchaseMate_Price,PurchaseMate_Sum) select Purchase_Id,Product_Id,PurchaseMate_Num,PurchaseMate_Price,PurchaseMate_Sum from #oo

if(@@ERROR<>0)
begin
rollback tran t12 --回滚
return 0
end
else
begin
commit tran t12 --提交
return 1
end

购物车事务