stored procedure iterations - 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

SQL Server Development (2000)

stored procedure iterations

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-29 : 08:16:23

Bobby writes "I'm wanting to run the SP for each SchedID retrieved in the --Get SchedID query. It's running it over-and-over for the same SchedID. I see that I'm not "looping" over the query. I'm stumped. Any help would be appreciated.

DECLARE @Iterations int

select @Iterations = MAX(scheduleid)
from scheduledtests
where status = 'completed'

DECLARE @SchedID int, @X tinyINT
SET @X=1

WHILE @X <= @Iterations
BEGIN

--Get SchedID
SELECT @SchedID = ScheduleID
FROM ScheduledTests
WHERE status = 'completed'

--run p_Calc
EXEC p_Calc @SchedID

SET @X=@X+1
END"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 08:28:00

Are scheduleID continuous?
I have a feeling their not because otherwise you could just loop on the exec statement.

Can you post some sample data and results.
Can you post the p_Calc, you might not need all the looping.

This always returns you a set and you're assigning the top 1 of the set to the variable so it will always be the same.

SELECT @SchedID = ScheduleID
FROM ScheduledTests
WHERE status = 'completed'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources