Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

What Your SP Can Return

By Robert Marda,

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.
Total article views: 9855 | Views in the last 30 days: 7
 
Related Articles
FORUM

full cycle structure...command select *

full cycle structure...command select *

FORUM
FORUM

Triggers Examples

Triggers Examples

FORUM

SSIS error while executing in command line

SSIS error while executing in command line

FORUM

update command

update command

Tags
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones