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:
CREATE PROCEDURE #prc_DoSomeMultiStatementComplicatedWork @rowcount INT AS BEGIN SET NOCOUNT ON DECLARE @status INT = 1 BEGIN TRY BEGIN TRANSACTION INSERT INTO DestinationTable ( Col1, Col2) SELECT TOP (@rowcount) Col1, Col2 FROM SourceTable ORDER BY ID DELETE SourceTable WHERE ID IN ( SELECT TOP (@rowcount) ID FROM SourceTable ORDER BY ID ) IF @@ROWCOUNT = 0 BEGIN SET @status = 0 END COMMIT TRANSACTION END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage, ERROR_NUMBER() AS ErrorNumber SET @status = 0 IF XACT_STATE() <> 0 BEGIN ROLLBACK END END CATCH RETURN @status END GO
Now the good part – i`ve created a global temp table to hold my stop flag:
CREATE TABLE ##tmp_stop_loop(stop_flag BIT) INSERT ##tmp_stop_loop VALUES (0)
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.
DECLARE @status INT = 1 DECLARE @stop_flag BIT = 0 DECLARE @rowcount INT = 10000 -- while status = OK and stop_flag has not been set WHILE (@status = 1 AND @stop_flag = 0) BEGIN -- execute the procedure EXECUTE @status = #prc_DoSomeMultiStatementComplicatedWork @rowcount -- check the if stop_flag was set to 1 - stop SET @stop_flag = NULL SELECT @stop_flag = stop_flag FROM ##tmp_stop_loop PRINT '@stop_flag = ' + CAST(@stop_flag AS CHAR) END GO
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:
UPDATE ##tmp_stop_loop SET stop_flag = 1
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.
Simple and cool solution.
You can do this using a time limit too (personal implementation)..
[]’s