Looping through rows in a stored procedure

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)

Looping through rows in a stored procedure

Author

Topic

VelocityMarty
Starting Member

6 Posts

Posted - 2002-08-01 : 12:25:59

I need help with doing the following in a stored procedure and executing it daily at 11:55 PM

select (id, namefield, expirationdate)
from tableA
where getdate() > tableA.expirationdate

then for each row that is returned insert a new row in tableB with the namefield and an adjusted id
I have the chunk of sql to adjust the id.

How do I loop through the selected rows from tableA, run the id modification sql, then insert the row into tableB?

Lastly, how do I schedule this stored_procedure to execute every day at 11:55 PM?

Thank you so much!
-Marty

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-08-01 : 12:33:04

solution in 2 parts...

1.
set dateformat dmy (or mdy if preferred.)

insert into tableb (cola, colb, colc)
select (adjusted(id), namefield, expirationdate)
from tableA
where getdate() > tableA.expirationdate

2. investigate the SQL scheduler...part of Enterprise Manager....wil lallow stored procedures to be run as per any one-off/recurring schedule.