Introduction

In this article I plan to show you what you can do with the RETURN command. Some of what I show here is easily found in BOL. I have taken this opportunity to expand on what is described there and show you some new ways to use the RETURN command. Simply put the RETURN command will stop the execution of a stored procedure and return one integer value that you can capture in a variable and use.

I will be using the pubs database throughout this article.

Example 1

Here is a simple way to use the RETURN command:

CREATE PROCEDURE spReturnExample1 AS
IF (SELECT pub_id FROM titles) = 1
SELECT * FROM titles
IF @@ERROR <> 0
RETURN 1
ELSE
RETURN 0
GO
DECLARE @ReturnValue int
EXEC @ReturnValue = spReturnExample1
SELECT @ReturnValue AS ReturnValue

The variable @ReturnValue will be 1 if an error occurs with the above if statement or a 0 if no error is encountered. I designed it so that it will generate an error unless you only have one row in the titles table. To make it not generate an error simply add TOP 1 after SELECT in the query enclosed by parenthesis.

Example 2

The return value is not limited to a constant. You can customize an error message and use the error number to determine which error message is displayed. In the below example the value returned will be the error number caused by the if statement:

CREATE PROCEDURE spReturnExample2 AS
IF (SELECT pub_id FROM titles) = 1
SELECT * FROM titles
RETURN @@ERROR
GO
DECLARE @ReturnValue int
EXEC @ReturnValue = spReturnExample2
IF @ReturnValue = 512
PRINT 'The query in the if statement pulled more than one record which is forbidden when using the equals sign.'

After execution you will see the standard error SQL Server gives you. After that you get the customized error you create based on the error number returned by the stored procedure.

Example 3

You can do calculations on the value being returned. Just keep in mind your result will always be an integer. The below example shows you a stored procedure that simply returns you a random number:

CREATE PROCEDURE spRandomNumber AS
RETURN (LTRIM(STR(CAST(RAND()*10000 AS int))) + LTRIM(STR(DATEPART(ms, GETDATE()))) * 2) + 1
GO
DECLARE @RandomNumber int
EXEC @RandomNumber = spRandomNumber 
PRINT @RandomNumber

I can’t say this SP has any advantage over an SP where you replace the word RETURN with PRINT or SELECT since all execute without even registering a millisecond of elapsed time on my computer. If you use SELECT then you will be able to see an execution plan. So, for this example it just depends on how you want your number returned: With a column name, as a value without a column name or a value returned using the RETURN command.

Example 4

This last example demonstrates that you can even use a query with the RETURN command:

CREATE PROCEDURE spQueryValueReturn (@title_id char(6) = '') AS
RETURN (SELECT Royalty FROM titles WHERE title_id = @title_id)
GO
DECLARE @Royalty int
EXEC @Royalty = spQueryValueReturn @title_id = 'PC8888'
PRINT @Royalty

I compared the speed of this SP with one that is the same except it doesn’t use the RETURN command. I found that the one without the RETURN went about 2 miliseconds faster than the one with the RETURN command. The above code simply demonstrates that you can get the results of a query directly returned with the RETURN command should that be the way you want to get one value.

Conclusions

You can set the value you want returned by the RETURN command using many different means. In this article I showed how you can return a constant, variable, computed value, or a value from a query.