0

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
  • So many questions. (1) Why are @StartDate/@EndDate/@cust_id strings? (2) Why so few semi-colons? (3) Why SET NOCOUNT OFF;? (4) Why not a cursor with LOCAL FAST_FORWARD? (5) Why is @update_sql not nvarchar? (6) Why EXEC() and not sys.sp_executesql? (7) Why NOLOCK on an update (and NOLOCK without WITH in other places)? – Aaron Bertrand May 9 '16 at 18:16
4

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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