One developer's SQL blog

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

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.

Twitter

Linkedin

Facebook