Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2009
    Posts
    11

    Unanswered: resetting @@Rowcount to zero after a sql statement

    I am deleting a series of records from different tables and outputting the time it takes and the number of records deleted.

    It seems @@rowcount is keeping the info from the previous delete statement throughout the routine and from one customerid to the next.

    I am trying to reset @@rowcount to zero so on the next delete statement it records the correct number of records deleted.

    My code and sample output is below. It is in a stored procedure . Any help would be appreciated.

    Code:

    CREATE PROCEDURE deletecustomerKH AS
    BEGIN
    -- Declare the variables to store the values returned by FETCH.

    DECLARE @cuid varchar(50)

    --Declare the variable times to be held

    declare @t1 datetime, @t2 datetime, @t3 datetime, @t4 datetime, @t5 datetime, @t6 datetime
    declare @t7 datetime, @t8 datetime, @t9 datetime, @t10 datetime, @t11 datetime, @t12 datetime
    declare @t13 datetime, @t14 datetime, @t15 datetime, @t16 datetime, @t17 datetime, @t18 datetime

    --Declare the variables to hold the time difference

    declare @tim1 varchar(20), @tim2 varchar(20), @tim3 varchar(20), @tim4 varchar(20), @tim5 varchar(20), @tim6 varchar(20), @tim8 varchar(20), @tim9 varchar(20), @tim10 varchar(20)

    --Declare the variables to hold the row count for each delete statement

    declare @rcount1 int, @rcount2 int, @rcount3 int, @rcount4 int, @rcount5 int, @rcount6 int, @rcount8 int, @rcount9 int, @rcount10 int

    --Declare the variabel to hold the output to file statement

    DECLARE @cmd varchar(300), @cmd2 varchar(300), @cmd3 varchar(300), @cmd4 varchar(300), @cmd5 varchar(300), @cmd6 varchar(300), @cmd7 varchar(300), @cmd8 varchar(300), @cmd9 varchar(300), @cmd10 varchar(300)


    DECLARE contact_cursor CURSOR FOR
    SELECT cusid FROM deletecid

    OPEN contact_cursor

    -- Perform the first fetch and store the values in variables.
    -- Note: The variables are in the same order as the columns
    -- in the SELECT statement.

    FETCH NEXT FROM contact_cursor
    INTO @cuid

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN



    set @t1 = getdate()

    DELETE trImageText
    FROM trImageText JOIN trItem ON trImageText.TableID =
    trItem.trItemID INNER JOIN
    deletecid ON trItem.trCustomerID = deletecid.cusid

    set @t2 = getdate()
    set @tim1 = datediff(second,@t1,@t2)
    set @rcount1 = @@rowcount



    SET @cmd7 = 'echo Current Customer: ' + @cuid + ' >> c:\Output\deletedrecords.txt'
    SET @cmd = 'echo Table ImageText Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount1 AS varchar(10)) + ' Time (seconds): ' + @tim1 + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd7
    EXEC master..xp_cmdshell @cmd




    set @t3 = getdate()

    DELETE trItem
    FROM trItem JOIN deletecid ON trItem.trCustomerID = deletecid.cusid

    set @t4 = getdate()
    set @tim2 = datediff(second,@t3,@t4)
    set @rcount2 = @@rowcount



    SET @cmd2 = 'echo Table Text Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount2 AS varchar(10)) + ' Time (seconds): ' + @tim2 + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd2




    set @t5 = getdate()

    DELETE trEmployee
    FROM trEmployee JOIN deletecid ON trEmployee.trcustomerid =
    deletecid.cusid

    set @t6 = getdate()
    set @tim3 = datediff(second,@t5,@t6)
    set @rcount3 = @@rowcount



    SET @cmd3 = 'echo Table Employee Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount3 AS varchar(10)) + ' Time (seconds): ' + @tim3 + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd3


    set @t7 = getdate()

    DELETE trDepartment
    FROM trDepartment JOIN deletecid ON trDepartment.trcustomerid =
    deletecid.cusid

    set @t8 = getdate()
    set @tim4 = datediff(second,@t7,@t8)
    set @rcount4 = @@rowcount



    SET @cmd4 = 'echo Table Department- Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount4 AS varchar(10)) + ' Time (seconds): ' + @tim4 + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd4



    set @t13 = getdate()

    DELETE trWOHead
    FROM trWOHead JOIN deletecid ON trWOHead.trcustomerid = deletecid.cusid


    set @t14 = getdate()
    set @tim8 = datediff(second,@t13,@t14)
    set @rcount8 = @@rowcount

    SET @cmd8 = 'echo Table WOHead - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount8 AS varchar(10)) + ' Time (seconds): ' + @tim8 + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd8



    set @t15 = getdate()

    DELETE trWODelete
    FROM trWODelete JOIN deletecid ON trWODelete.trcustomerid = deletecid.cusid


    set @t16 = getdate()
    set @tim9 = datediff(second,@t15,@t16)
    set @rcount9 = @@rowcount

    SET @cmd9 = 'echo Table WODelete - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount9 AS varchar(10)) + ' Time (seconds): ' + @tim9 + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd9



    set @t17 = getdate()

    DELETE trWODetail
    FROM trWODetail JOIN trItem ON trWODetail.tritemid = trItem.trItemID INNER JOIN deletecid ON trItem.trCustomerID = deletecid.cusid


    set @t18 = getdate()
    set @tim10 = datediff(second,@t17,@t18)
    set @rcount10 = @@rowcount

    SET @cmd10 = 'echo Table WODetail - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount10 AS varchar(10)) + ' Time (seconds): ' + @tim10 + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd10



    set @t9 = getdate()

    DELETE trCustomer
    FROM trCustomer JOIN deletecid ON trCustomer.trcustomerid =
    deletecid.cusid

    set @t10 = getdate()
    set @tim5 = datediff(second,@t9,@t10)
    set @rcount5 = @@rowcount

    SET @cmd5 = 'echo Table Customer - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount5 AS varchar(10)) + ' Time (seconds): ' + @tim5 + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd5



    set @t11 = getdate()

    DELETE trServices
    FROM trServices JOIN deletecid ON trServices.trcustomerid =
    deletecid.cusid

    set @t12 = getdate()
    set @tim6 = datediff(second,@t11,@t12)
    set @rcount6 = @@rowcount

    SET @cmd6 = 'echo Table Services - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount6 AS varchar(10)) + ' Time (seconds): ' + @tim6 + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd6



    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM contact_cursor
    INTO @cuid
    END

    CLOSE contact_cursor
    DEALLOCATE contact_cursor

    END



    OUTPUT:

    Current Customer: 3260
    Table ImageText Customer ID: 3260 Number of Records deleted: 1 Time (seconds): 136
    Table Text Customer ID: 3260 Number of Records deleted: 1 Time (seconds): 96
    Table Employee Customer ID: 3260 Number of Records deleted: 1 Time (seconds): 96
    Table Department- Customer ID: 3260 Number of Records deleted: 1 Time (seconds): 0
    Table WOHead - Customer ID: 3260 Number of Records deleted: 1 Time (seconds): 5
    Table WODelete - Customer ID: 3260 Number of Records deleted: 1 Time (seconds): 0
    Table WODetail - Customer ID: 3260 Number of Records deleted: 1 Time (seconds): 0
    Table Customer - Customer ID: 3260 Number of Records deleted: 1 Time (seconds): 1
    Table Services - Customer ID: 3260 Number of Records deleted: 1 Time (seconds): 2

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quick question...
    Is this procedure deleting a customer record and all its child records?


    EDIT: ignore this, it was a lead up question to what I asked you here: http://www.dbforums.com/microsoft-sq...tput-file.html
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nah, you're just using it wrong.
    Code:
    DELETE trImageText 
           FROM  trImageText JOIN trItem ON trImageText.TableID =
    trItem.trItemID  INNER JOIN
           deletecid ON trItem.trCustomerID = deletecid.cusid
    
          set @t2 = getdate()
          set @tim1 = datediff(second,@t1,@t2)
          set @rcount1 = @@rowcount
    The red line is reading the number of rows affected by the PREVIOUS statement i.e. the blue line.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2009
    Posts
    11
    So if I put the line set @rcount1 = @@rowcount on just after each of the delete statements it will return the correct number of records deleted - I do not need to set the @@rowcount to zero before each of the other delete statements in the routine??

    SEE BELOW the change I made....


    DELETE trImageText
    FROM trImageText JOIN trItem ON trImageText.TableID =
    trItem.trItemID INNER JOIN
    deletecid ON trItem.trCustomerID = deletecid.cusid


    set @rcount1 = @@rowcount
    set @t2 = getdate()
    set @tim1 = datediff(second,@t1,@t2)


    Thanks for the help!!!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes.
    Another alternative and more efficient (though not standard SQL):
    Code:
    DELETE trImageText 
           FROM  trImageText JOIN trItem ON trImageText.TableID =
    trItem.trItemID  INNER JOIN
           deletecid ON trItem.trCustomerID = deletecid.cusid
    
    
          SELECT @t2 = getdate()
          , @tim1 = datediff(second,@t1,@t2)
          , @rcount1 = @@rowcount
    Note that even @tim1 works as @t2 has been evaluated. As this is a single statement, @@rowcount is the value that you want.

    @@rowcount is actually a system variable so you can't set it even if you want to.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2009
    Posts
    11
    Thank You!!!

    Wow such a simple change and I spent hours trying to figure it out... It works great.

    Your knowledge is appreciated!!!
    KH

  7. #7
    Join Date
    Feb 2009
    Posts
    11

    Additional Issue - in output file

    I am having an additional problem it seems that when there is more than one cusid in the deletecid table the values for the time and rowcount seems to be totaled for all of the cusid and the value is written to the top record in the table. For some reason as it goes to through the while loop it adds to the old values.

    The deletecid table rows are below:
    148
    345



    OUTPUT:

    Current Customer: 148
    Table ImageText Customer ID: 148 Number of Records deleted: 8076 Time (seconds): 1116
    Table Text Customer ID: 148 Number of Records deleted: 10810 Time (seconds): 731
    Table Employee Customer ID: 148 Number of Records deleted: 776 Time (seconds): 16
    Table Department- Customer ID: 148 Number of Records deleted: 1540 Time (seconds): 3
    Table WOHead - Customer ID: 148 Number of Records deleted: 2019 Time (seconds): 53
    Table WODelete - Customer ID: 148 Number of Records deleted: 25 Time (seconds): 2
    Table WODetail - Customer ID: 148 Number of Records deleted: 0 Time (seconds): 0
    Table Customer - Customer ID: 148 Number of Records deleted: 2 Time (seconds): 2
    Table Services - Customer ID: 148 Number of Records deleted: 174 Time (seconds): 5
    Current Customer: 345
    Table ImageText Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0
    Table Text Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0
    Table Employee Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0
    Table Department- Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0
    Table WOHead - Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0
    Table WODelete - Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0
    Table WODetail - Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0
    Table Customer - Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0
    Table Services - Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0


    I was trying to output the number of records deleted and the time it takes per cusid

    code is below for the output above:

    CREATE PROCEDURE deletecustomerKH AS
    BEGIN
    -- Declare the variables to store the values returned by FETCH.

    DECLARE @cuid varchar(50)

    --Declare the variable times to be held

    declare @t1 datetime, @t2 datetime, @t3 datetime, @t4 datetime, @t5 datetime, @t6 datetime
    declare @t7 datetime, @t8 datetime, @t9 datetime, @t10 datetime, @t11 datetime, @t12 datetime
    declare @t13 datetime, @t14 datetime, @t15 datetime, @t16 datetime, @t17 datetime, @t18 datetime

    --Declare the variables to hold the time difference

    declare @tim1 int, @tim2 int, @tim3 int, @tim4 int, @tim5 int, @tim6 int, @tim8 int, @tim9 int, @tim10 int

    --Declare the variables to hold the row count for each delete statement

    declare @rcount1 int, @rcount2 int, @rcount3 int, @rcount4 int, @rcount5 int, @rcount6 int, @rcount8 int, @rcount9 int, @rcount10 int

    --Declare the variabel to hold the output to file statement

    DECLARE @cmd varchar(300), @cmd2 varchar(300), @cmd3 varchar(300), @cmd4 varchar(300), @cmd5 varchar(300), @cmd6 varchar(300), @cmd7 varchar(300), @cmd8 varchar(300), @cmd9 varchar(300), @cmd10 varchar(300)


    DECLARE contact_cursor CURSOR FOR
    SELECT cusid FROM deletecid

    OPEN contact_cursor

    -- Perform the first fetch and store the values in variables.
    -- Note: The variables are in the same order as the columns
    -- in the SELECT statement.

    FETCH NEXT FROM contact_cursor
    INTO @cuid

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN

    set @rcount1 = 0
    set @tim1 = 0

    set @t1 = getdate()

    DELETE trImageText
    FROM trImageText JOIN trItem ON trImageText.TableID =
    trItem.trItemID INNER JOIN
    deletecid ON trItem.trCustomerID = deletecid.cusid

    set @rcount1 = @@rowcount

    set @t2 = getdate()
    set @tim1 = datediff(second,@t1,@t2)




    SET @cmd7 = 'echo Current Customer: ' + @cuid + ' >> c:\Output\deletedrecords.txt'
    SET @cmd = 'echo Table ImageText Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount1 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim1 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd7
    EXEC master..xp_cmdshell @cmd


    set @rcount2 = 0
    set @tim2 = 0

    set @t3 = getdate()

    DELETE trItem
    FROM trItem JOIN deletecid ON trItem.trCustomerID = deletecid.cusid

    set @rcount2 = @@rowcount

    set @t4 = getdate()
    set @tim2 = datediff(second,@t3,@t4)




    SET @cmd2 = 'echo Table Text Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount2 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim2 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd2


    set @rcount3 = 0
    set @tim3 = 0


    set @t5 = getdate()

    DELETE trEmployee
    FROM trEmployee JOIN deletecid ON trEmployee.trcustomerid =
    deletecid.cusid

    set @rcount3 = @@rowcount

    set @t6 = getdate()
    set @tim3 = datediff(second,@t5,@t6)




    SET @cmd3 = 'echo Table Employee Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount3 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim3 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd3


    set @rcount4 = 0
    set @tim4 = 0

    set @t7 = getdate()

    DELETE trDepartment
    FROM trDepartment JOIN deletecid ON trDepartment.trcustomerid =
    deletecid.cusid

    set @rcount4 = @@rowcount

    set @t8 = getdate()
    set @tim4 = datediff(second,@t7,@t8)




    SET @cmd4 = 'echo Table Department- Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount4 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim4 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd4


    set @rcount8 = 0
    set @tim8 = 0

    set @t13 = getdate()

    DELETE trWOHead
    FROM trWOHead JOIN deletecid ON trWOHead.trcustomerid = deletecid.cusid

    set @rcount8 = @@rowcount

    set @t14 = getdate()
    set @tim8 = datediff(second,@t13,@t14)


    SET @cmd8 = 'echo Table WOHead - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount8 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim8 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd8


    set @rcount9 = 0
    set @tim9 = 0

    set @t15 = getdate()

    DELETE trWODelete
    FROM trWODelete JOIN deletecid ON trWODelete.trcustomerid = deletecid.cusid


    set @rcount9 = @@rowcount

    set @t16 = getdate()
    set @tim9 = datediff(second,@t15,@t16)


    SET @cmd9 = 'echo Table WODelete - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount9 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim9 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd9


    set @rcount10 = 0
    set @tim10 = 0

    set @t17 = getdate()

    DELETE trWODetail
    FROM trWODetail JOIN trItem ON trWODetail.tritemid = trItem.trItemID INNER JOIN deletecid ON trItem.trCustomerID = deletecid.cusid


    set @rcount10 = @@rowcount

    set @t18 = getdate()
    set @tim10 = datediff(second,@t17,@t18)


    SET @cmd10 = 'echo Table WODetail - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount10 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim10 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd10


    set @rcount5 = 0
    set @tim5 = 0

    set @t9 = getdate()

    DELETE trCustomer
    FROM trCustomer JOIN deletecid ON trCustomer.trcustomerid =
    deletecid.cusid

    set @rcount5 = @@rowcount

    set @t10 = getdate()
    set @tim5 = datediff(second,@t9,@t10)


    SET @cmd5 = 'echo Table Customer - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount5 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim5 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd5

    set @rcount6 = 0
    set @tim6 = 0

    set @t11 = getdate()

    DELETE trServices
    FROM trServices JOIN deletecid ON trServices.trcustomerid =
    deletecid.cusid

    set @rcount6 = @@rowcount

    set @t12 = getdate()
    set @tim6 = datediff(second,@t11,@t12)


    SET @cmd6 = 'echo Table Services - Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount6 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim6 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd6



    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM contact_cursor
    INTO @cuid
    END

    CLOSE contact_cursor
    DEALLOCATE contact_cursor

    END
    Last edited by akatech; 02-11-09 at 04:48.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you cut down the code to the minimum required to illustrate your point (it does the same thing x times - can you just include one instance?). Also, can you wrap in code tags as this preserves formatting and makes reading easier.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2009
    Posts
    11
    Not sure if this is what your looking for , I am new to working with SQL 2000


    CODE:

    set @rcount1 = 0
    set @tim1 = 0

    set @t1 = getdate()

    DELETE trImageText
    FROM trImageText JOIN trItem ON trImageText.TableID =
    trItem.trItemID INNER JOIN
    deletecid ON trItem.trCustomerID = deletecid.cusid

    set @rcount1 = @@rowcount

    set @t2 = getdate()
    set @tim1 = datediff(second,@t1,@t2)




    SET @cmd7 = 'echo Current Customer: ' + @cuid + ' >> c:\Output\deletedrecords.txt'
    SET @cmd = 'echo Table ImageText Customer ID: '+ @cuid + ' Number of Records deleted: ' + CAST(@rcount1 AS varchar(10)) + ' Time (seconds): ' + CAST(@tim1 AS varchar(20)) + ' >> c:\Output\deletedrecords.txt'
    EXEC master..xp_cmdshell @cmd7
    EXEC master..xp_cmdshell @cmd


    OUTPUT:

    Current Customer: 148
    Table ImageText Customer ID: 148 Number of Records deleted: 8076 Time (seconds): 1116

    Current Customer: 345
    Table ImageText Customer ID: 345 Number of Records deleted: 0 Time (seconds): 0


    There should be values in customer 345 and for some reason the code is adding the values to the output in customer 148

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm not sure. I don't use cursors ever.
    code tags: [ code] [ /code] (minus spaces).

    What does this return (note use of code tags!):
    Code:
    SELECT cusid, COUNT(*)
    FROM trImageText
    WHERE cusid IN(148, 345)
    GROUP BY cusid
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2009
    Posts
    11
    What is happening is that @@rowcount and the @t1, @t2 values are holding the values from the first cuid (148) of the delete statement and when it goes to the second cuid (345) it is adding to the number of records deleted and the time lapsed to the cuid(148) output statement.

    Example cuid(148) should be 1000 records deleted and it took 500 seconds

    cuid (345) should be 2000 records deleted and it took 900 seconds

    instead it is outputting

    cuid(148) 3000 records deleted and it took 1400 seconds

    cuid (345) 0 records deleted and it took 0 seconds



    Not sure why?

  12. #12
    Join Date
    Apr 2007
    Posts
    183
    It is a JOIN thing. You are joing the complete deleteCID table for first iteration so there are no records left for second iteration.
    And by doing so, there is no need for a CURSOR loop.

    All said and done here
    SQL Server Forums - @@Rowcount issue inside while loop

  13. #13
    Join Date
    Apr 2007
    Posts
    183
    This is how your stored procedure should look like
    Code:
    CREATE PROCEDURE dbo.usp_DeleteCustomerKH
    AS
    
    SET NOCOUNT ON
    
    DECLARE @cuID VARCHAR(50),
    	@dt DATETIME,
    	@rc INT,
    	@cmd VARCHAR(300)
    
    DECLARE curContact CURSOR FOR 
    		SELECT	cusID
    		FROM	deleteCID
    
    OPEN curContact
    
    FETCH NEXT
    FROM		curContact
    INTO		@cuID
    
    WHILE @@FETCH_STATUS = 0
    	BEGIN
    		SET @cmd = 'echo Current Customer: ' + @cuID + ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		SET @dt = GETDATE()
    
    		DELETE		w
    		FROM		trImageText AS w
    		INNER JOIN	trItem ON trItem.trItemID = w.TableID
    		WHERE		trItem.trCustomerID = @cuID
    
    		SET	@cmd = 'echo Table ImageText Customer ID: ' + @cuID
    				+ ' Number of Records deleted: ' + CAST(@@ROWCOUNT AS VARCHAR(11))
    				+ ' Time (seconds): ' + CAST(DATEDIFF(SECOND, @dt, GETDATE()) AS VARCHAR(11))
    				+ ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		SET @dt = GETDATE()
    
    		DELETE		w
    		FROM		trWODetail AS w
    		INNER JOIN	trItem ON trItem.trItemID = w.tritemid
    		WHERE		trItem.trCustomerID = @cuID
    
    		SET	@cmd = 'echo Table WODetail Customer ID: ' + @cuID
    				+ ' Number of Records deleted: ' + CAST(@@ROWCOUNT AS VARCHAR(11))
    				+ ' Time (seconds): ' + CAST(DATEDIFF(SECOND, @dt, GETDATE()) AS VARCHAR(11))
    				+ ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		SET @dt = GETDATE()
    
    		DELETE
    		FROM	trWOHead
    		WHERE	trcustomerid = @cuID
    
    		SET	@cmd = 'echo Table WOHead Customer ID: ' + @cuID
    				+ ' Number of Records deleted: ' + CAST(@@ROWCOUNT AS VARCHAR(11))
    				+ ' Time (seconds): ' + CAST(DATEDIFF(SECOND, @dt, GETDATE()) AS VARCHAR(11))
    				+ ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		SET @dt = GETDATE()
    
    		DELETE
    		FROM	trWODelete
    		WHERE	trcustomerid = @cuID
    
    		SET	@cmd = 'echo Table WODelete Customer ID: ' + @cuID
    				+ ' Number of Records deleted: ' + CAST(@@ROWCOUNT AS VARCHAR(11))
    				+ ' Time (seconds): ' + CAST(DATEDIFF(SECOND, @dt, GETDATE()) AS VARCHAR(11))
    				+ ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		SET @dt = GETDATE()
    
    		DELETE
    		FROM	trItem
    		WHERE	trCustomerID = @cuID
    
    		SET	@cmd = 'echo Table Item Customer ID: ' + @cuID
    				+ ' Number of Records deleted: ' + CAST(@@ROWCOUNT AS VARCHAR(11))
    				+ ' Time (seconds): ' + CAST(DATEDIFF(SECOND, @dt, GETDATE()) AS VARCHAR(11))
    				+ ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		SET @dt = GETDATE()
    
    		DELETE	
    		FROM	trEmployee
    		WHERE	trcustomerid = @cuID
    
    		SET	@cmd = 'echo Table Employee Customer ID: ' + @cuID
    				+ ' Number of Records deleted: ' + CAST(@@ROWCOUNT AS VARCHAR(11))
    				+ ' Time (seconds): ' + CAST(DATEDIFF(SECOND, @dt, GETDATE()) AS VARCHAR(11))
    				+ ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		SET @dt = GETDATE()
    
    		DELETE
    		FROM	trDepartment
    		WHERE	trcustomerid = @cuID
    
    		SET	@cmd = 'echo Table Department Customer ID: ' + @cuID
    				+ ' Number of Records deleted: ' + CAST(@@ROWCOUNT AS VARCHAR(11))
    				+ ' Time (seconds): ' + CAST(DATEDIFF(SECOND, @dt, GETDATE()) AS VARCHAR(11))
    				+ ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		SET @dt = GETDATE()
    
    		DELETE
    		FROM	trCustomer
    		WHERE	trcustomerid = @cuID
    
    		SET	@cmd = 'echo Table Customer Customer ID: ' + @cuID
    				+ ' Number of Records deleted: ' + CAST(@@ROWCOUNT AS VARCHAR(11))
    				+ ' Time (seconds): ' + CAST(DATEDIFF(SECOND, @dt, GETDATE()) AS VARCHAR(11))
    				+ ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		SET @dt = GETDATE()
    
    		DELETE 
    		FROM	trServices
    		WHERE	trcustomerid = @cuID
    
    		SET	@cmd = 'echo Table Services Customer ID: ' + @cuID
    				+ ' Number of Records deleted: ' + CAST(@@ROWCOUNT AS VARCHAR(11))
    				+ ' Time (seconds): ' + CAST(DATEDIFF(SECOND, @dt, GETDATE()) AS VARCHAR(11))
    				+ ' >> c:\Output\deletedrecords.txt'
    
    		EXEC master..xp_cmdshell @cmd
    
    		FETCH NEXT
    		FROM		curContact
    		INTO		@cuID
    	END
    
    CLOSE		curContact
    DEALLOCATE	curContact

  14. #14
    Join Date
    Apr 2007
    Posts
    183
    More comments in the link on SQLTeam above... :-)

  15. #15
    Join Date
    Feb 2009
    Posts
    11
    Thanks for all the help I got the answer to my issue from a post that I did on another forum here is the link to the solution if someone needs.

    SQL Server Forums - @@Rowcount issue inside while loop

    Thanks again, I am new to the forum scene not sure if I am supposed to link to another forum..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •