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
Best Answer
Add
AND COALESCE(' + @fieldname + ',0) <> 1
to yourWHERE
clause. You're updating the same 5,000 rows over and over and over and over...