游标第一次应用

                      

　　　　　　　　　　　　　
                       遇到问题不慌 仔细看问题，查找到原因才可以改，理解前后逻辑，碰到一块 熟悉这快得逻辑和技术知识

                       DECLARE    @Table_MaxID INT,@WarehouseId INT
                      DECLARE @TempTable table (wid int,maxId int)

                      DECLARE My_Cursor CURSOR 
                      FOR (SELECT WarehouseId FROM  dbo.Warehouse WITH (NOLOCK) WHERE ISNULL(IsDeleted,0)=0) 
                      OPEN My_Cursor; 
                      FETCH NEXT FROM My_Cursor INTO @WarehouseId;
                      WHILE @@FETCH_STATUS = 0
                      BEGIN
                          EXEC    @Table_MaxID = [dbo].[cp_BuildIdentity]  @tp_typeName = N'Inventory'    
                          INSERT INTO @TempTable (wid,maxId) VALUES(@WarehouseId,@Table_MaxID)
                          FETCH NEXT FROM My_Cursor INTO @WarehouseId; 
                      END
                      CLOSE My_Cursor;
                      DEALLOCATE My_Cursor; 

             UPDATE [dbo].[ItemMaintain]
           SET [SyncDateTime] = @SyncDateTime,
           IsSentWarehouse=1
           WHERE  ItemCode=@ItemCode
           IF NOT EXISTS (SELECT InventoryId FROM dbo.InventoryForEdit WHERE ItemCode=@ItemCode)
           BEGIN
             
               INSERT INTO dbo.InventoryForEdit
               
                       ( InventoryId ,
                         StoreId ,
                         ItemType ,
                         ItemId ,
                         ItemCode,
                             Warehouseid
                       )
                SELECT maxId,1,ItemType,ItemId,ItemCode,Warehouseid
                FROM dbo.ItemMaintain,dbo.Warehouse WITH (NOLOCK)
                LEFT JOIN @TempTable ON wid=Warehouse.Warehouseid
                WHERE ItemId=@ItemCode AND     Warehouse.IsDeleted=0    
           END

 

转载于:https://www.cnblogs.com/q101301/p/4589525.html