采购主子表添加事物SQL存储过程跟大家分享

--SQL事物添加主子表，希望大家可以看看，有不对的地方可以指出来，新手非常感谢大家，互相帮助！共同学习！
create proc Purchase_Add
(
   @Purchase_curom int,
   @Purchase_date datetime,
   @Purchase_State varchar(100),
   @SumMoney int,
   @Purchase_Remark varchar(max),
   @Supplier_Id int,
   @WareHose_Id int,
   @Employee_Id int,
   @xml xml
)
as
begin tran--开始执行事务
--主表添加数据
insert into Purchase(Purchase_curom,Purchase_date,Purchase_State,SumMoney,Purchase_Remark,Supplier_Id,WareHose_Id,Employee_Id)
values(@Purchase_curom,@Purchase_date,@Purchase_State,@SumMoney,@Purchase_Remark,@Supplier_Id,@WareHose_Id,@Employee_Id)

declare @id int,@pointer int--声明变量
set @id=@@IDENTITY;--获取主表中的自增列

begin
create table #ster--创建临时表
(
        Purchase_Id int,
		Product_Id int,
		PurchaseMate_Num int,
		PurchaseMate_Price decimal

)
end
execute sp_xml_preparedocument @pointer output,@xml --sp_xml_preparedocument语式
--将XML解析的数据循坏添加到临时表
insert into #ster(Product_Id,PurchaseMate_Num,PurchaseMate_Price)
select Product_Id,PurchaseMate_Num,PurchaseMate_Price from openxml(@pointer,'/mm/xx')--系统程序sp_xml_preparedocument  指向'/ss/cc'
with(

          Product_Id int,
		PurchaseMate_Num int,
		PurchaseMate_Price decimal
)
update #ster set Purchase_Id=@id--修改临时表的主键
insert into PurchaseMate select Product_Id,PurchaseMate_Num,PurchaseMate_Price from #ster--将临时表中的数据全部添加到子表中

if(@@ERROR<>0)--判断以上是否执行成功
begin 
 rollback tran--操作失败返回0，执行回滚操作
 return 0;
 end
 else
 begin
      commit tran t12 --提交事物
      return 1;--执行成功返回1
 end

　　

 

转载于:https://www.cnblogs.com/mxx328/p/6051195.html