1

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
2
  • 1
    Side note you should always use sp_ExecuteSQL @SQL as opposed to EXEC (@SQL) for security reasons.
    – J.D.
    Commented Jan 15, 2021 at 19:32
  • 1
    Yes, thanks. That was a copy and paste oversight.
    – WAMLeslie
    Commented Jan 15, 2021 at 19:43

2 Answers 2

1

Add

IF convert(varchar(11),CURRENT_TIMESTAMP,108) >= '06:56:59' BREAK;

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.

0

Thanks for the response. This works to escape the current batched query but unfortunately it doesn't end the stored procedure completely. It basically skips the query and moves to the next.

I actually just got it to work using a similar solution but with a THROW, but not sure if this is a proper way to handle it.

Does this make sense or is it the wrong way to go about it?

IF CAST(GETDATE() AS TIME)>'07:00:00' THROW 51000, 'Job reached cutoff time.', 0;
2
  • That's actually a proper use case of throwing an exception, so that's fine.
    – J.D.
    Commented Jan 15, 2021 at 20:02
  • 2
    Excellent! Thanks again for the feedback.
    – WAMLeslie
    Commented Jan 15, 2021 at 20:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.