Safe exit from WHILE loop using ##global temp tables

Loops, SQL Server – these two things don’t go along as best friends, but every once in a WHILE, there’s no other way around. If I also add transactions, i surely want to have full control over it.

Not once i had to stop a loop doing some processing after it was 30 mins in, and it was using transactions, and I hate pressing the red square button. I just hate that. Waiting for current command to be rolled back, rolling back any open transactions… Recently, when again i had to use a WHILE again, I started to ask myself how can I skip the red button if I need to stop the loop in the middle of it. Answer came pretty fast – use a global temp table to keep the flag into it, and when I need to stop it, just update the flag from a different session.

First, i created a temp stored procedure to handle the “heavy” stuff i needed – just moving rows from one table to another for an almost good example:

Now the good part – i`ve created a global temp table to hold my stop flag:

And the actual loop. Before it starts, need to default the @status and @stop_flag, so that it enters the loop.
In the loop, execute the temp procedure first, and take stop_flag value from global temp table.

This runs and runs and runs, 100 mil rows to move, and runs some more, and I decide to stop it even tho’ is not yet done – I go and open another window and run the following:

Looking at results:

Ok, that’s nice, I’ve stopped my while loop when I needed, but it also completed current “step”, so I don’t have to worry about open transactions left over, I don’t have to wait for transaction to rollback – i just need to wait for it to complete, i didn’t had to push the red button, query says “Completed successfully”.

I bet this can be done better, but for me, it served my needs when I needed.

  1. Simple and cool solution.
    You can do this using a time limit too (personal implementation)..

Leave a Comment

Trackbacks and Pingbacks: