on July 9, 2018
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!
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.)
1) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?
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.
2) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?
RETURN
PRINT 2;
GO
sql
- 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."
### 3) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?
``` sql
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.
4) Given this code, what appear in the Messages window of SSMS after you execute dbo.Test?
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.