存储过程 订单中子表多条数据与 主表 相结合

create proc proc_AddPuchase
(
    @Purchase_Id int,
    @Purchase_curom int,
    @Purchase_date datetime,
    @Purchase_State varchar(30),
    @SumMoney int,
    @Purchase_Remark varchar(max),
    @Supplier_Id int,
    @WareHose_Id int,
    @Employee_Id int,
    @xml xml--子表传过来的是XML类型
)
as
begin tran
insert into Purchase(Purchase_Id,Purchase_curom,Purchase_date,Purchase_State,SumMoney,Purchase_Remark,Supplier_Id,WareHose_Id,Employee_Id) values(@Purchase_Id,@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 #oo
    (
        Purchase_Id int,
        Product_Id int,
        PurchaseMate_Num int,
        PurchaseMate_Price decimal
    )
    end
    execute sp_xml_preparedocument @pointer output,@xml  --sp_xml_preparedocument语式
    insert into #oo(Purchase_Id,Product_Id,PurchaseMate_Num,PurchaseMate_Price)
    select Purchase_Id,Product_Id,PurchaseMate_Num,PurchaseMate_Price from openxml(@pointer,'/ss/cc') --系统程序sp_xml_preparedocument  指向'/ss/cc'
   with(
        Purchase_Id int,Product_Id int,PurchaseMate_Num int,PurchaseMate_Price decimal
   )
   
   
   
   update #oo set Purchase_Id=@id  --修改临时表中的外键
   
   insert into PurchaseMate select * from #oo  --将临时 表中的数据全部添加到OrderSonInfo中

   if(@@ERROR>0)  --判断以上操作是否执行完毕
   begin
        rollback tran --回滚
        return 0
   end
   else
   begin
       commit tran --提交
       return 1
   end

转载于:https://www.cnblogs.com/MJK-ONG/p/6052436.html