While Loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

While Loop

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-29 : 13:30:54

I want to write the following query but it gives error. How can I correct this. I want to loop through the result set and select the values of each row and do some manipulations.

DECLARE @warehouse_id NUMERIC(23,10)
DECLARE @qty NUMERIC(23,10)

WHILE EXISTS(SELECT @warehouse_id=warehouse_id,@qty=quantity FROM Order_Items_Warehouse AS oiw WHERE order_item_id = @order_item_id AND product_id = @item_id)

BEGIN

UPDATE [Product_Warehouse]
SET
commited = commited - @qty,
on_hand = on_hand - @qty
WHERE warehouse_id=@warehouse_id

END

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-29 : 13:41:04

Here's how to do what you requested. You do not need a while, but regardless this is probably not the right approach for a inventory managment system.



UPDATE a
SET Commited = a.Commited - b.Qty
,On_Hand = a.On_Hand - b.Qty
from
  Product_Warehouse a
Inner Join
  Order_Items_Warehouse b
on   a.WareHouse_ID = b.WareHouse_ID

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-29 : 13:44:14

Thanks for replying. What is the best way for inventory management?

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-29 : 13:54:11

What I can think of is we would need to put the Locks on the rows to be updated and Commit or Rollback Transaction accordingly.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-29 : 14:02:54

In a inventory managment system can be as complex or as simple as you want and it depends on your needs. A simple rule of thumb to use though is to not make your data dependent on 1 updated record, it's a potential for disastor.

Keep all the inventory transactions in a seperate table to increase decrease the value, then update the total.

So in it's simplest form.


IF OBJECT_ID('tempDb..#Items') IS NOT NULL DROP TABLE #Items
Select 1 as ItemID,'My Item 1' as ItemDesc, 3 as QtyOnHand 
Into #Items
union all
Select 2,'My Item 2', 10 as QtyOnHand union all
Select 3,'My Item 3', 78 as QtyOnHand



IF OBJECT_ID('tempDb..#InventoryAdjustments') IS NOT NULL DROP TABLE #InventoryAdjustments
Select 1 as ItemID,'My Item 1' as ItemDesc, 3 as Adjustments
into #InventoryAdjustments
union all
Select 2,'My Item 2', 10  union all
Select 3,'My Item 3', 78 Union all
Select 1 as ItemID,'My Item 1' as ItemDesc, -3 as Adjustments union all
Select 2,'My Item 2', -1  union all
Select 3,'My Item 3', -20 Union all
Select 1 as ItemID,'My Item 1' as ItemDesc, 7 as Adjustments union all
Select 2,'My Item 2',  10 union all
Select 3,'My Item 3', 99 


Update a
set QtyOnHand = b.Adjustments
from
#Items a
Inner Join
(
	Select ItemID,sum(Adjustments) as Adjustments
	from
	#InventoryAdjustments aa
	group by ItemID
) b
on a.ItemID = b.ItemID

select * from #Items

This way you are tracking all the modifications and the QTY ON HAND is far less prone to a error.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-29 : 14:24:41

Thanks a lot Vinnie for the nice explaination.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources