批量update代替游标

今天写了一个SQL,觉得挺牛逼的,被人一句话 毙了

 DECLARE @ProductId INT , @ProductCount INT  
            --提仓调整冻结库存
            IF @Adjusttype=1
            BEGIN
                DECLARE orderDetail_CURSOR CURSOR FOR
                SELECT ProductId,ProductCount FROM dbo.OrderDetail WITH(NOLOCK) WHERE OrderID=     @OrderID
                OPEN orderDetail_CURSOR
                FETCH NEXT FROM orderDetail_CURSOR INTO @ProductId,@ProductCount
                WHILE @@FETCH_STATUS = 0 
                    BEGIN
                        --改商品冻结库存 (原订单仓库)
                        UPDATE  dbo.InventoryForEdit
                        SET     FrozenStock = FrozenStock - @ProductCount
                        WHERE   ItemId = @ProductId
                        AND ItemType = 1 and WarehouseId = @OriginalWarehouseID

                        --改商品冻结库存 (换选仓后的仓库)
                        UPDATE  dbo.InventoryForEdit
                        SET     FrozenStock = FrozenStock + @ProductCount
                        WHERE   ItemId = @ProductId
                        AND ItemType = 1 and WarehouseId = @WarehouseId
                        
                        FETCH NEXT FROM orderDetail_CURSOR INTO @ProductId,@ProductCount
                    END
                CLOSE orderDetail_CURSOR
                DEALLOCATE orderDetail_CURSOR
            END
            ELSE --提仓失败 回滚数据
            BEGIN
                DECLARE orderDetail_CURSOR CURSOR FOR
                SELECT ProductId,ProductCount FROM dbo.OrderDetail WITH(NOLOCK) WHERE OrderID=@OrderID
                OPEN orderDetail_CURSOR
                FETCH NEXT FROM orderDetail_CURSOR INTO @ProductId,@ProductCount
                WHILE @@FETCH_STATUS = 0 
                    BEGIN
                        --改商品冻结库存 (原订单仓库)
                        UPDATE  dbo.InventoryForEdit
                        SET     FrozenStock = FrozenStock + @ProductCount
                        WHERE   ItemId = @ProductId
                        AND ItemType = 1 and WarehouseId = @OriginalWarehouseID

                        --改商品冻结库存 (换选仓后的仓库)
                        UPDATE  dbo.InventoryForEdit
                        SET     FrozenStock = FrozenStock - @ProductCount
                        WHERE   ItemId = @ProductId
                        AND ItemType = 1 and WarehouseId = @WarehouseId
                        
                        FETCH NEXT FROM orderDetail_CURSOR INTO @ProductId,@ProductCount
                    END
                CLOSE orderDetail_CURSOR
                DEALLOCATE orderDetail_CURSOR
            END

下面是更改后的SQL,而且参数,传的更少了。执行效率更高了,道行还是浅啊。

                --改商品冻结库存 (原订单仓库)
                UPDATE InventoryForEdit
                SET 
                InventoryForEdit.FrozenStock=InventoryForEdit.FrozenStock - O.ProductCount
                FROM  OrderDetail AS O WITH(NOLOCK) 
                WHERE InventoryForEdit.ItemId=O.ProductId AND InventoryForEdit.ItemType=O.ItemType AND InventoryForEdit.WarehouseId=@OriginalWarehouseID
                AND O.OrderID=@OrderID

                  --改商品冻结库存 (换选仓后的仓库)
                UPDATE InventoryForEdit
                SET 
                InventoryForEdit.FrozenStock=InventoryForEdit.FrozenStock +O.ProductCount
                FROM  OrderDetail AS O WITH(NOLOCK) 
                WHERE InventoryForEdit.ItemId=O.ProductId AND InventoryForEdit.ItemType=O.ItemType AND InventoryForEdit.WarehouseId=@WarehouseId
                AND O.OrderID=@OrderID

觉得自己很牛逼的SQL ,被老大一句话干掉了,应该问他怎么想到的

posted @ 2015-07-14 21:53  小菜鸟飞飞  阅读(799)  评论(0编辑  收藏  举报