WHILE Loop causing Error - 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 causing Error

Author

Topic

luvdairish
Starting Member

6 Posts

Posted - 2007-05-17 : 09:00:05

I'm creating a trigger. I can create it when I don't have the WHILE Loop syntax in there, but when I put the WHILE Loop in (that is the ONLY change), I get error:

Incorrect syntax near '='.

Can someone look at my code:

declare @orderid as int
declare @status as int
declare @oQTY as int
declare @newQTY as int
declare @oProductID as int
select @orderid = orderid, @status = [status] from inserted where [status] = 1
if (@status = 1)
begin
while (select @oProductID = ProductID, @oQTY = QTY from Custodial_Order_Details
where OrderID = @orderid)
begin
select @newQTY = sum(QTY - @oQTY) from custodial_inventory where ProductID = @oProductID

update custodial_inventory
set QTY = @newQTY
where ProductID = @oProductID
end
end

THANK YOU!!!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-17 : 09:18:45

What condition do you want to put for the WHILE loop?

Something like this?

while exists(select * from Custodial_Order_Details
where OrderID = @orderid)
begin
...
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

luvdairish
Starting Member

6 Posts

Posted - 2007-05-17 : 09:28:22

Yes! That worked. But how do I grab those values (ProductID and QTY that we replaced with the *) and put them into variables for use in the proceeding statements?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-17 : 10:44:02

[code]

while exists(select * from Custodial_Order_Details
where OrderID = @orderid)
begin
select @oProductID = ProductID, @oQTY = QTY from Custodial_Order_Details
where OrderID = @orderid
...
end
[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources