Difference between Output Parameter and Return Value in Stored Procedure

create procedure Out_test1 (@inValue int, @OutValue int output)  
as
begin
set @OutValue = @InValue
end
declare @x int ;
exec Out_test1 @inValue=3, @OutValue = @x output
select @x 'Interger'  

create procedure Return_test1(@username varchar(20))  
as
begin
declare @returnvalue int
insert into testUser(UserName) values(@username)
set @returnvalue=@@ERROR
end
declare @x int
exec @x=Return_test1 'aaa'
select @x 'Return_value'  

create procedure Out_test2 (@inValue int, @OutValue1 int output,@OutValue2 int output)
as
begin
set @OutValue1 = @InValue
set @OutValue2=@inValue
end
declare @x int,@y int ;
exec Out_test2 @inValue=3, @OutValue1 = @x output,@OutValue2=@y output
select @x 'int',@y 'int'

Output

Note, An output parameter can return one or more values.

With multiple return values with the same data type.

create procedure Return_test2(@inValue1 int,@inValue2 int)  
as  
begin  
return @inValue1  
return @inValue2  
end  
declare @x int,@y int  
exec @x=Return_test2 @inValue1=1,@inValue2=2  
select @x 'int'

Output

Here, returning multiple return values is not possible. In the output, we are able to return only one value.

Note, A return value can return only one value.

Multiple output parameters with different data types.

create procedure Out_test3 (@OutValue1 int output,@OutValue2 DateTime output,@outValue3 varchar(10) output)
as
begin
set @OutValue1 = 10
set @OutValue2=GETDATE()
set @outValue3='test'
end
declare @x int,@y datetime,@z varchar(10);
exec Out_test3 @OutValue1=@x output,@OutValue2=@y
output,@outValue3=@z output
select @x 'interger',@y 'datetime',@z 'varchar'

Output

Note, An output parameter returns data with any data type.

Return value with the different data types.

create procedure Return_test3(@inValue varchar(10))
as
begin
return @inValue
end
declare @x varchar(10)
exec @x=Return_test3 @inValue='test'
select @x

We will get an error indicating conversion failed when converting the varchar value "test" to data type int because the return status variable is an integer. We have passed a value of type varchar hence we got an error.

Note, the return value returns data of only an integer data type.

The following is when to use output parameters and return values in Stored Procedures:

• When you want to return one or more items with a data type then it is better to use an output parameter.
• Generally, use an output parameter for anything that needs to be returned.
• When you want to return only one item with only an integer data type then it is better to use a return value.
• Generally, the return value is only to inform the success or failure of the Stored Procedure.
• A return value of 0 indicates success and any non-zero value indicates failure.

Conclusion

I hope you like this article and understand the difference between an output parameter and a return value in a Stored Procedure.