The following provides a portion of SQL that demonstrates how to leverage the MAX_MINUTES parameter of the IN_DB_UTIL framework to implement a means for defining an end time for picking up new work.
This should only be used for situations where it is acceptable that any remaining tasks are not executed if time has expired.
Note that the following excerpt is an incomplete example and does not include the declaration of variables and error handling and session initialization.
-- Define a cursor containing work items
DECLARE MY_CURSOR CURSOR LOCAL READ_ONLY FOR SELECT...
-- Open the cursor and begin processing the work items
OPEN MY_CURSOR;
FETCH NEXT FROM MY_CURSOR INTO...
WHILE @@FETCH_STATUS = 0
BEGIN
-- Check MAX_TIME for the identifier
EXEC @V_CHECK_TIME = IN_DB_UTIL_SP_CHECK_TIME @V_IDENTIFIER, @V_DEBUG;
-- Check if the specified maximum duration (minutes) has elapsed
IF (@V_MAX_MINUTES = 0 OR (@V_MAX_MINUTES > 0 AND @V_CHECK_TIME = 0))
BEGIN
-- Do your work here...
END;
ELSE IF @V_CHECK_TIME = 1 -- Time has expired.
BEGIN
PRINT NCHAR(10)+N'MAX_MINUTES has elapsed.';
GOTO SUCCESS;
END;
-- Fetch the next item of work
FETCH NEXT FROM MY_CURSOR INTO...
END;
-- Successful Completion
SUCCESS:
BEGIN
CLOSE MY_CURSOR;
DEALLOCATE MY_CURSOR;
SET @V_RETURN_VAL = 0;
GOTO DONE;
END;
DONE:
BEGIN
RETURN(@V_RETURN_VAL);
END;