output parameter vs return [Resolved]

Posted by Saranpselvam under Sql Server on 9/5/2014 | Points: 10 | Views : 584 | Status : [Member] | Replies : 1
using return values, we can only return integers, and that too, only one integer. It is not possible, to return more than one value using return values, where as output parameters, can return any datatype and an sp can have more than one output parameters. I always prefer, using output parameters, over RETURN values.

In general, RETURN values are used to indicate success or failure of stored procedure, especially when we are dealing with nested stored procedures.Return a value of 0, indicates success, and any nonzero value indicates failure


Posted by: Bandi on: 9/5/2014 [Member] [MVP] Platinum | Points: 50


Yes what you wrote exactly true...

RETURN option will returns the value ( 0 or non-zero) which indicates SP xecution status. Additionally, if you have only one integer output from the SP , we can make use of RETURN option...

OUTPUT clause can be used for returning more than one different/same type of data from SP.

RETURN does the job of OUTPUT in some cases. RETURN is used to return integers. You can have multiple OUTPUT, but one RETURN.

create proc dbo.TestOutput (@InValue varchar(20), @OutValue varchar(20) output)
set @OutValue = @InValue

declare @x varchar(20)
exec TestOutput @InValue = 'abc', @OutValue = @x output
select @x

Using return variable:

create proc dbo.TestReturn (@InValue varchar(20))
return @InValue

declare @x varchar(20)
exec @x = TestReturn @InValue = 'abc'
select @x

Mark This Response as Answer

Saranpselvam, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response