Posted on

Using RETURN in Transact SQL: Quiz Results

RETURN is a “control of flow” language keyword in Transact-SQL. It’s very useful for managing when code does — and does NOT — get executed in procedural code, and it can also be used to communicate the status from procedural code.

But not everyone knows how to use RETURN, and learning about it can be awkward because of some quirks of SQL Server Management Studio. It is well worth taking the time to get used to it, though!

Want to test your knowledge?

Take this quiz on RETURN, then head back here to see how you compare to other folks who took the quiz.

Note: I’m going to implement a change soon so that only the four most recent quizzes are open to everyone. I am still going to post questions and answers in follow up posts, but the fun interactive bit won’t be as open (after a month for each quiz).

So now is a good time to go through my quiz archive if you haven’t already!

Quiz results and Discussion of RETURN

I changed the wording on question #4 on July 4 after Nick posted a message about the wording — and he was totally right, there was more than one defensible correct answer.

Changing the wording did not significantly change the average score, it turns out. The average percentage dropped from 65% to 64% once that question was clarified. (That’s not a bad percentage at all — I think a lot of folks aren’t used to using RETURN, and hopefully the quiz made them curious on how it can be useful.)

RETURN
GO

PRINT 2;
GO
  • Correct: 292 (78%)
  • Incorrect: 84 (22%)

Answer: 2

The key to this question is that RETURN controls flow within a given batch, and the default batch separator is the word “GO”.

If you want to stop the execution of a script you’re running in SSMS, the RETURN keyword won’t stop everything if you have more than one batch.

RETURN

PRINT 2;
GO
  • Correct: 249 (66%)
  • Incorrect: 127 (34%)

Answer: Commands completed successfully.

In this question, RETURN is shown inside a single batch. It will stop the flow of execution before the PRINT statement, and SSMS will simply report, “Commands completed successfully.”

IF 1 = 1
BEGIN
    RETURN;
END

PRINT 2;
  • Correct: 256 (68%)
  • Incorrect: 120 (32%)

Answer: Commands completed successfully.

This time we have everything in one batch statement again, but we have the RETURN inside of a BEGIN / END block.

The gist of this question is: does RETURN cause it to exit only the BEGIN/END block, or that entire block of code?

The answer is that it causes it to exit from the entire block of code, it will never reach the PRINT statement.

CREATE OR ALTER PROC dbo.test
AS
    RETURN 1;
GO

EXEC dbo.test;
GO
  • Correct: 29 (58%)
  • Incorrect: 21 (42%)

Answer: Commands completed successfully.

The wording in this post is the “corrected” wording. The original wording was less clear, so I’ve reported the correct/incorrect ratios for July 5 – 9, after the wording was changed.

This one may seem silly, but I think it’s very important to know that SSMS will not report anything in the Messages tab in the case. It doesn’t mean that RETURN didn’t work, it simply means that the SSMS interface doesn’t show it!

If you want to capture the value that RETURN sends back and do something with it, you need to assign it to something, like this:

CREATE OR ALTER PROC dbo.test
AS
    RETURN 1;
GO

DECLARE @RESULT TINYINT
EXEC @RESULT = dbo.test;

PRINT @RESULT
GO

Used in this way, RETURN can be incredibly useful when it comes to returning status from procedural code.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.