Why does query execution continue after RETURN?

Question

Consider following script:

IF OBJECT_ID('tempdb.dbo.#INTERMED', 'U') IS NOT NULL
    DROP TABLE #INTERMED; 

IF OBJECT_ID('tempdb.dbo.#INTERMED1', 'U') IS NOT NULL
    DROP TABLE #INTERMED1; 

PRINT 'Inserting INTO #INTERMED'
GO

SELECT 11 AS Col1
INTO #INTERMED

RETURN -- Why does execution continue below this line?

PRINT 'Inserting INTO #INTERMED1' -- This doesn't print anything
GO
SELECT 'Testing testing 123' AS Col2 
INTO #INTERMED1

SELECT * FROM #INTERMED1 i

When you run it in SSMS you will notice that RETURN is ignored, PRINT statement after RETURN doesn't do anything and then execution continues.

Can someone explain why? I would expect it to exit immediately after RETURN.

I did find that it is somehow related to GO statements because if I commented out all GO statements it behaves as expected (exits after RETURN) but I still don't have an explanation.

Answer 1

GO is not part of the SQL Language. It's a batch separator used by Management Studio, and adopted as a convention by some other tools as well, but it has no special meaning in the language itself. Try to use it in a stored procedure and see what I mean.

Therefore, what happens is you have one batch the looks like this:

IF OBJECT_ID('tempdb.dbo.#INTERMED', 'U') IS NOT NULL
    DROP TABLE #INTERMED; 

IF OBJECT_ID('tempdb.dbo.#INTERMED1', 'U') IS NOT NULL
    DROP TABLE #INTERMED1; 

PRINT 'Inserting INTO #INTERMED'

It does it's thing, and then you have a new batch that looks like this:

SELECT 11 AS Col1
INTO #INTERMED

RETURN -- Why does execution continue below this line?

PRINT 'Inserting INTO #INTERMED1' -- This doesn't print anything

It runs to the RETURN statement, at which point the batch, and only that batch, returns/finishes. However, there is still one more batch to run:

SELECT 'Testing testing 123' AS Col2 
INTO #INTERMED1

SELECT * FROM #INTERMED1 i

Again, this is a whole new batch. The previous RETURN statement means nothing. It's like you called three methods in sequence.

---

I also saw this in the comments:

The reason I had GO in it is to actually have PRINT statements output something while script is still executing.

There's a better way. Look into the RAISERROR statement:

RAISERROR('My Progress Message',0,1) WITH NOWAIT