Delete from table variable MS SQL

Question

I'm trying to delete rows from a table variable one by one every loop in my stored procedure but there are times that it keeps looping and cannot delete the record. The record is there even when I tried to PRINT the value. I dont get any errors when my delete statement is executed.

Are there instances that delete from the Table Variable is delayed that is causing the loop to not end?

Here is my code:

    --DECLARATIONS
    declare @temp_table table
    (
    rid int identity(1,1),
    Account_Code varchar(255),
    PRIMARY KEY (rid)
    )
    declare @row_count int = 0
    declare @current_id int
    -----------------------------

    delete from @temp_table

    insert into @temp_table
        select distinct a.Account_Code from MyTable a

    set @row_count =(select COUNT(*) from @temp_table)

    print 'TABLE ROWS COUNT:'+ cast(@row_count as varchar(100))

    while(@row_count <> 0)
    begin
    set @current_id = (select top 1 rid from @temp_table)
    print 'Current ID in Process:'+cast(@current_id as varchar(100))

    /*
    Some Processes Here.....
    */

    delete from @temp_table where rid = @current_id
    set @row_count =(select COUNT(*) from @temp_table)
    print 'TABLE ROWS COUNT:'+ cast(@row_count as varchar(max))
    end

This is what I get from printing The Values:

    TABLE ROWS COUNT:21
    Current ID in Process: 10403
    TABLE ROWS COUNT:20
    Current ID in Process: 10404
    TABLE ROWS COUNT:19
    Current ID in Process: 10405
    TABLE ROWS COUNT:18
    Current ID in Process: 10406
    Current ID in Process: 10406
    Current ID in Process: 10406
    Current ID in Process: 10406
    Current ID in Process: 10406

The script then loops at 10406.

NOTE: I already used the @temp_table for other process before this part of the script that is why rid value is now at 10400 and up

score 1 · Answer 1 · answered Oct 10 '12 at 09:50

I can't fit this in a comment, but I believe you have glossed over some important bits.

while(@row_count <> 0)
begin
set @current_id = (select top 1 rid from @temp_table)
print 'Current ID in Process:'+cast(@current_id as varchar(100))

/*
Some Processes Here.....
*/

if ... condition ...
    begin
    delete from @temp_table where rid = @current_id
    set @row_count =(select COUNT(*) from @temp_table)
    print 'TABLE ROWS COUNT:'+ cast(@row_count as varchar(max))
    end
end -- while loop

There must be something like the above, otherwise short of a goto, it cannot skip the print 'TABLE.... The point being the condition being FALSE causing the loop not to "advance".

score 0 · Answer 2 · answered Oct 12 '12 at 06:07

My bad. I already found what is causing the infinite loop. I have this code before I delete the @current_id from my table variable

BEGIN TRY
    /*
        calculations...
    */
END TRY
BEGIN CATCH
    continue;
    print 'ERROR'
END CATCH;

The continue in my CATCH block skips the delete statement.

Delete from table variable MS SQL

2 Answers2