SQL Server stored procedure running in infinite loop

Question

I am running this stored procedure that updates a column. It runs in an infinite loop. When I run the internal query separately, it completes just fine. When I cancel the execution of the stored procedure after it has ran for a while, it shows several 5000 row batches affected and asks if I want to commit. Please help.

CREATE PROCEDURE [dbo].[UPDATE_INDATE_PARENT_EMAILS_AND_EDOCS_revised]
    @FieldName VARCHAR(16), 
    @StartDate varchar(15), 
    @EndDate varchar(15), 
    @CustodianIDs varchar(500)
AS
    PRINT ('NUMBER OF CUSTODIANS:')

    DECLARE @XML AS XML
    DECLARE @Delimiter AS CHAR(1) =','

    SET @XML = CAST(('<X>'+REPLACE(@CustodianIDs,@Delimiter ,'</X><X>')+'</X>') AS XML)

    DECLARE @cust_tbl TABLE (custodianid INT)

    INSERT INTO @cust_tbl
        SELECT N.value('.', 'INT') AS custodianid 
        FROM @XML.nodes('X') AS T(N)

Declare @cust_id    varchar(20)
Declare @update_sql varchar(max)

declare cust_curs  cursor for 
SELECT * FROM @cust_tbl
ORDER BY 1
open cust_curs
fetch next from cust_curs into @cust_id
while @@FETCH_STATUS = 0

BEGIN -- custodian loop

set @update_sql = '

SET NOCOUNT ON;

Select ID into #DOCS from tbldoc (nolock) where custodianid = ' + @CUST_ID + '

SET NOCOUNT OFF;

WHILE (1=1) 
BEGIN

    BEGIN TRANSACTION

update TOP(5000) tbldoc set ' + @fieldname + ' = 1
from dbo.tbldoc d (nolock)
join #docs d2 on d2.id = d.id
where (
(d.AttachPID in 
(select d3.AttachPID from tblDoc d3 (nolock)
join #docs d4 on d4.id = d3.id 
where isnull(d3.Exclude,0) = 0 and d3.AttachPID > 0 and d3.AttachLvl = 0 and
(d3.datesent between ''' + @StartDate + ''' and ''' + @EndDate + ''' or 
 d3.datelastmod between ''' + @StartDate + ''' and ''' + @EndDate + ''' or 
 d3.datecreated between ''' + @StartDate + ''' and ''' + @EndDate + ''')))
or 
(isnull(d.Exclude,0) = 0  and d.AttachPID = 0 and d.AttachLvl = 0 and
 (d.datesent between ''' + @StartDate + ''' and ''' + @EndDate + ''' or 
  d.datelastmod between ''' + @StartDate + ''' and ''' + @EndDate + ''' or 
  d.datecreated between ''' + @StartDate + ''' and ''' + @EndDate + ''')))

       IF @@ROWCOUNT = 0 
            BEGIN 
            COMMIT TRANSACTION 
            BREAK 
        END 
    COMMIT TRANSACTION 
END
'



PRINT ('UPDATED INDATE FOR CUSTODIANID ' + @CUST_ID)   

exec (@update_sql)

IF OBJECT_ID('tempdb..#DOCS', 'U') IS NOT NULL
  DROP TABLE #DOCS;

  FETCH NEXT FROM cust_curs  INTO @cust_id
END

CLOSE cust_curs
DEALLOCATE cust_curs

Answer 1

Add AND COALESCE(' + @fieldname + ',0) <> 1 to your WHERE clause. You're updating the same 5,000 rows over and over and over and over...