Query to change 2 statements into a batching job I have a SQL server agent job setup to delete records from a parent table where the date is greater than 2 weeks and also delete all records from a child table where the corresponding ID no longer exists in the parent table (basically a FK relationship without the actual relationship). 

The issue is that even though this job takes under a minute to run it is being deadlocked every other week.  The code you see below is my attempt to setup batching to break it up.  My issue right now is that the entire statement completes successfully, but for whatever reason no records are being deleted from the parent table despite it having a very simple WHERE class (greater than 2 weeks).  It is almost like it is completing the first loop, going to the second, but doing nothing, and then saying it completed.  I am sure I am missing something very simple that is causing me to overlook this issue.   

SET NOCOUNT ON

USE database
GO

DECLARE 
    @sql nvarchar(1000)
    ,@i int
    ,@ii int
    ,@batchSize int
    ,@totalDeleteLimit int
    ,@totalDeleteCurrentCount int
    ,@totalDeleteCurrentCount2 int
    ,@interval nvarchar(100)

SELECT 
    @i = 1
    , @ii = 1
    , @batchSize = 10000
    , @totalDeleteLimit = 2000000
    , @totalDeleteCurrentCount = 0
    ,@totalDeleteCurrentCount2 = 0
    , @interval = '00:00:00.01'

--PRINT CAST(CAST(GETDATE() as Datetime2(7)) as nvarchar(100)) + N' - Start Logging Purge'

BEGIN TRY
WHILE @i &lt;&gt; 0
BEGIN
    SELECT @sql = N'DELETE TOP(' + CAST(@batchSize as nvarchar(10)) + N') 
    FROM ChildTable WHERE LogID IN (SELECT LogID
                FROM ParentTable
                WHERE [Timestamp] &lt;= dateadd(week,-2,getdate()))'

    --PRINT @sql
    EXEC sp_executesql @sql

    SET @i = @@ROWCOUNT

    --print @i
    SELECT @totalDeleteCurrentCount = @totalDeleteCurrentCount + @i;

    WAITFOR DELAY @interval;

    IF(@totalDeleteCurrentCount &gt;= @totalDeleteLimit)
    BEGIN
        PRINT N'-- Limit Reached on 1st loop';
        BREAK;
    END
    ELSE
        CONTINUE;
WHILE @ii &lt;&gt; 0
BEGIN
    SELECT @sql = N'DELETE TOP(' + CAST(@batchSize as nvarchar(10)) + N') 
    FROM ParentTable
    WHERE [Timestamp] &lt;= dateadd(week,-2,getdate())'

    --PRINT @sql
    EXEC sp_executesql @sql

    SET @ii = @@ROWCOUNT

    --print @ii
    SELECT @totalDeleteCurrentCount2 = @totalDeleteCurrentCount2 + @ii;

    WAITFOR DELAY @interval;

    IF(@totalDeleteCurrentCount2 &gt;= @totalDeleteLimit)
    BEGIN
        PRINT N'-- Limit Reached';
        BREAK;
    END
    ELSE
        CONTINUE;
END
END
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return 
    -- error information about the original error that 
    -- caused execution to jump to the CATCH block.
    RAISERROR (
        @ErrorMessage, -- Message text.
        @ErrorSeverity, -- Severity.
        @ErrorState -- State.
    );
END CATCH

--PRINT CAST(CAST(GETDATE() as Datetime2(7)) as nvarchar(100))  + N' -- Total Purged: ' + CAST(@totalDeleteCurrentCount as nvarchar(20))
Your first while loop 

WHILE @i &lt;&gt; 0
BEGIN


Encompasses the second while loop as well. 

The docs on break  show that:


  BREAK exits the current WHILE loop


.

Meaning that you are closing the entire while loop including the nested while loop (WHILE @ii &lt;&gt; 0). You have to close the first while loop before opening the second one.

Add an END before opening the next while loop, for example:

    IF(@totalDeleteCurrentCount &gt;= @totalDeleteLimit)
    BEGIN
        PRINT N'-- Limit Reached on 1st loop';
        BREAK;
    END
    ELSE
        CONTINUE;
END
WHILE @ii &lt;&gt; 0