I have a SQL job which executes a stored procedure. The proc loops through a number of databases, then executes a series of dynamic SQL queries to archive/delete a bunch of tables in each database. Each of the dynamic queries itself loops because the deletes are happening in smaller batches. (As below)
This job runs overnight but I'd like it to stop if it runs to 7:00am, since that will run into production hours.
I know that I could create a separate additional job that runs "dbo.sp_stop_job" to stop my archive job, but I'd prefer to do it within the archive stored procedure itself.
I'd like for the proc to exit at 7:00am regardless of whether the execution is between tables or if it's between delete batches within each table, but don't really want to have to code the same condition over and over all through it.
I know I might be asking a lot here, but any thoughts on this?
DECLARE CURSOR_DB CURSOR FAST_FORWARD FOR
SELECT DatabaseName
FROM #Databases
OPEN CURSOR_DB
FETCH NEXT FROM CURSOR_DB INTO @sourceDB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'DELETE tbl1 in batches'
EXEC (@SQL)
SET @SQL = 'DELETE tbl2 in batches'
EXEC (@SQL)
SET @SQL = 'DELETE tbl3 in batches'
EXEC (@SQL)
FETCH NEXT FROM CURSOR_DB INTO @sourceDB
END
CLOSE CURSOR_DB
DEALLOCATE CURSOR_DB
Best Answer
Add
Before fetching the new row.
This will stop the cursor after 06:56:59
The current timestamp will be converted in a 24h string and will be compared wuth the choosen time every day.