How to stop / exit SQL process on specific date time condition

If you have an SP been used to run periodically on night and you want to stop the process on the morning time to reduce the load on the system, initially you can configure a SQL job to start run on 11:55 PM  and end at 08:00 AM, this will work fine the process completed before the end time (08:00AM). But if the process not yet completed the SQL will not stop the job as excepted and the process will continue running.  

To achieve this requirement there are two different methods to do that and this will be depend on the SP structure

1- SQL while Loop used:

 In this case we will try to stop the loop on time by using the while condition and to do that we need to define the stop date time as variable and we will set this variable to the next day date and required time, e.g. if we want the SP to be stop on next day on 08:00AM and to today date 22nd November 2011 so the stop date time  should be 08:00AM  23nd November 2011. The following script define the @STOP_TIME and it will set it to the next day and time.

In the while loop system will check two conditions , 1^st next record to be processed available and  the date /time is before the next day 08:00AM, since system getting the record one by one to be processed so every time system will check the date /time condition and the system will stop the process when it reached to next day 08:00AM even if there are records need to be processed.

DECLARE @STOP_TIME DATETIM

SELECT @STOP_TIME=CAST( CONVERT (VARCHAR(10),DATEADD(D,1,GETDATE()),102)+ ' 08:00:00 AM' AS DATETIME)

SELECT  @NextID = MIN(ROWID) from Table_A (nolock) WHERE PROCESSED = 0

WHILE ISNULL(@NextID,0) <> 0 and GETDATE()< @STOP_TIME

BEGIN

      .........Process Data..........

      .........Process Data..........

      .........Process Data..........

      .........Process Data..........

     

      SELECT @NextID = MIN(ROWID) FROM  Table_A  (nolock)WHERE PROCESSED = 0

      if @NextID is null

            begin

                  set @NextID=0

            end

End  

 2-SQL Cursor /SQL while Loop used or not used

The following method can be used if you have while loop in the SP or not, in this method we need to create another SQL job which will be schedule to run daily on 08:00 AM and in this SQL job we will use sp_stop_job to stop the first SQL Job. But there is disadvantage of using this method specially if the If a first SQL is currently executing a step of type CmdExec or PowerShell, the process being run (for example, MyProgram.exe) is forced to end prematurely. Premature ending can result in unpredictable behavior such as files in use by the process being held open. Consequently, sp_stop_job should be used only in extreme circumstances if the job contains steps of type CmdExec or PowerShell.