事物游标临时表的使用

USE [Retailers]
GO
/****** Object:  StoredProcedure [dbo].[Proc_Orders_Add]    Script Date: 03/31/2017 16:38:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---新增订单
ALTER PROC [dbo].[Proc_Orders_Add]
(
@Admin_ID INT,
@Orders_Date DATETIME,
@Orders_Price DECIMAL(8),
@Freight DECIMAL(8),
@All_Total DECIMAL(8),
@Orders_State INT,
@Express_State INT,
@Customer_ID INT,
@Consignee_Name VARCHAR(20),
@Consignee_Phone VARCHAR(11),
@Express_ID INT,
@Consignee_Tel VARCHAR(11),
@Consignee_Address VARCHAR(50),
@Province_Num INT,
@City_Num INT,
@Area_Num INT,
@Consignee_Market VARCHAR(200),
@xml XML,
@xml1 XML
)
AS

 BEGIN TRY
 
  BEGIN TRAN--开始执行事务
  --添加主表数据
    BEGIN

          DECLARE @IdentityId INT,@Pointer INT,@Pointer1 INT--声明变量
          INSERT INTO Orders(Admin_ID,Customer_ID,Express_ID,Orders_Date,Orders_Price,Freight,All_Total,Orders_State,Express_State)
          VALUES(@Admin_ID,@Customer_ID,@Express_ID,Convert(datetime,@Orders_Date,120),@Orders_Price,@Freight,@All_Total,@Orders_State,@Express_State)
          SET @IdentityId=@@IDENTITY;--获取主表中的自增列
          
          INSERT INTO Consignee(Orders_ID,Customer_ID,Consignee_Name,Consignee_Phone,Province_Num,City_Num,Area_Num,Consignee_Address,Consignee_Tel,Consignee_Market)
          VALUES(@IdentityId,@Customer_ID,@Consignee_Name,@Consignee_Phone,@Province_Num,@City_Num,@Area_Num,@Consignee_Address,@Consignee_Tel,@Consignee_Market)
    END
    BEGIN
    CREATE TABLE #OrdersSub--创建销售订单临时表
    (
    Orders_ID INT,
    Goods_ID INT,
    Goods_Number INT,
    Discount DECIMAL(8),
    Real_Collection DECIMAL(8),
    OrdersSub_Date DATETIME
    )
    CREATE TABLE #Stock--创建修改预定量临时表
    (
        Stock_ID INT,
        Stock_Total INT
    )
    END

    EXECUTE sp_xml_preparedocument @pointer output,@xml  
    INSERT INTO #OrdersSub(Goods_ID,Goods_Number,Discount,Real_Collection,OrdersSub_Date)
    SELECT Goods_ID,Goods_Number,Discount,Real_Collection,OrdersSub_Date
    FROM OPENXML(@pointer,'/ss/cc')
    WITH
    (    
        Goods_ID INT,
        Goods_Number INT,
        Discount DECIMAL(8),
        Real_Collection DECIMAL(8),
        OrdersSub_Date DATETIME
        )
    UPDATE #OrdersSub SET Orders_ID=@IdentityId--修改临时表中的外键
    INSERT INTO OrdersSub(Orders_ID,Goods_ID,Goods_Number,Discount,Real_Collection,OrdersSub_Date) 
    SELECT Orders_ID,Goods_ID,Goods_Number,Discount,Real_Collection,OrdersSub_Date 
    FROM #OrdersSub
    
    EXECUTE sp_xml_preparedocument @pointer1 output,@xml1 
    INSERT INTO #Stock(Stock_ID,Stock_Total)
    SELECT Stock_ID,Stock_Total
    FROM OPENXML(@Pointer1,'/ss/cc')
    WITH
    (    
        Stock_ID INT,
        Stock_Total INT
    )
    
    DECLARE @Stock_ID int,@Stock_Total int
    Declare Mycursor Cursor FOR SELECT Stock_ID,Stock_Total FROM #Stock
    
    --打开游标
    Open Mycursor
    
    Fetch next from Mycursor into @Stock_ID ,@Stock_Total
    
        while @@FETCH_STATUS=0
        begin
            update Stock set Stock_Total=(CONVERT(Int,(SELECT Stock_Total FROM Stock WHERE Stock_ID=@Stock_ID))+CONVERT(int,@Stock_Total))
            WHERE Stock_ID=@Stock_ID
            
            fetch next from Mycursor into @Stock_ID,@Stock_Total--转到下一条游标，没有就会死循环
        end
        CLOSE Mycursor  --关闭游标 
        DEALLOCATE  Mycursor--释放游标    
    
    
        IF(@@ERROR<>0)  --判断以上操作是否执行完毕
           BEGIN
                rollback tran   --回滚
                return 0
           END
       ELSE
           BEGIN
               commit tran  --提交
               return 1
           END
    
END TRY
    
BEGIN CATCH
    
    DECLARE @Mes VARCHAR(2000)=ERROR_MESSAGE()--定义错误信息
    rollback tran   --回滚
                return 0
    PRINT(@Mes)
    
END CATCH