loop or iterate - 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 2000 Forums

Transact-SQL (2000)

loop or iterate

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2007-04-06 : 17:15:12

Is there someway I can do a for each or loop in my sp?
For example, I am running a select and for each one found, I want to run an update sql:

Declare
@dstID int

Select @dstID =DSTID from EmpCIP
WHERE EmpCIPID = 26

Update....

*for each @dstID run the update sql for that @dstID found

Can anyone show me how to do this? I cannot even find what keyword to look for in the books online

ann
Posting Yak Master

220 Posts

Posted - 2007-04-06 : 18:00:36

ok - I found the "While" statement, but when I use it, I get Incorrect syntax in my select statement next to the while:

Declare
@dstID int
,@desc varchar (1000)
,@reward varchar (1000)
,@target varchar (1000)
,@out varchar (1000)

While exists
(Select @dstID =DSTID from EmpCIP WHERE EmpCIPID = 26)

BEGIN

Select @desc = Description
,@reward = Rewarded
,@target = Target
,@out = Outstanding
From DSTGoals WHERE DSTID=@DSTID

UPDATE EmpCIP

SET
Description = @desc
,Threshold = @reward
,Target = @target
,Outstanding = @out
WHERE DSTID=@DSTID

END

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-06 : 18:06:46

please post your update statement, perhaps theres a way to avoid the loop and do the update in a batch..

************************
Life is short. Enjoy it.
************************

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-06 : 18:12:35

Try something like this:

UPDATE E
SET
Description = D.Description
,Threshold = D.Rewarded
,Target = d.target
,Outstanding = d.Outstanding
FROM EmpCIP E
JOIN DSTGoals D ON E.DSTID = D.DSTID
WHERE E.EmpCIPID = 26

the issue with your current code is, change this line :
(Select @dstID =DSTID from EmpCIP WHERE EmpCIPID = 26)

to

(Select * from EmpCIP WHERE EmpCIPID = 26)

EXISTS () will return TRUE or FALSE. So trying to assign the value to a variable within EXISTS function is incorrect.

************************
Life is short. Enjoy it.
************************

ann
Posting Yak Master

220 Posts

Posted - 2007-04-06 : 18:13:38

My update statement is in there:
BEGIN

Select @desc = Description
,@reward = Rewarded
,@target = Target
,@out = Outstanding
From DSTGoals WHERE DSTID=@DSTID

UPDATE EmpCIP
SET
Description = @desc
,Threshold = @reward
,Target = @target
,Outstanding = @out
WHERE DSTID=@DSTID
END

ann
Posting Yak Master

220 Posts

Posted - 2007-04-06 : 18:22:26

I tried your change "Select * from EmpCIP WHERE EmpCIPID = 26" with the new update statement - it is soooo slow. It is taking over 2 minutes to process tables that have less than 10 rows of data each.

Is there another method I can use?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-06 : 18:26:30

Not only would it be slow, I think it will go into infinite loop because the status of EmpCIPID = 26 is not being changed in the loop. So there is no exit point. Check out the UPDATE statement I provided if it helps.

************************
Life is short. Enjoy it.
************************

ann
Posting Yak Master

220 Posts

Posted - 2007-04-06 : 18:28:34

I am using your update statement - and you're right, it's just looping, never breaking. How do I get it to break?

ann
Posting Yak Master

220 Posts

Posted - 2007-04-06 : 18:50:25

I am now using the "Fetch" statement, and it seems to work along with your better update:

@EmpID int

as

Declare dst_cursor CURSOR FOR
Select * from EmpCIP WHERE EmpCIPID = @EmpID

OPEN dst_cursor

-- Perform the first fetch.
FETCH NEXT FROM dst_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE EmpCIP

SET
Description = D.Description
,Threshold = D.Rewarded
,Target = d.target
,Outstanding = d.Outstanding
FROM EmpCIP E
JOIN DSTGoals D ON E.DSTID = D.DSTID
WHERE E.EmpCIPID = 26
FETCH NEXT FROM dst_cursor
END

CLOSE dst_cursor
DEALLOCATE dst_cursor
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-06 : 19:18:34

You don't need a loop nor a select. You just need to use an update with a join:

UPDATE e
SET Description = d.Description
,Threshold = d.Rewarded
,Target = d.Target
,Outstanding = d.Outstanding
FROM EmpCIP e
INNER JOIN DSTGoals d
ON e.DSTID = d.DSTID
WHERE e.EmpCIPID = 26

Just one command!

Note: you may need to make slight adjustments as I whipped this up fast.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources