Difference between Output Parameter and Return Value in Stored Procedure

Introduction

In this article, we will learn about the difference between the Output Parameter and Return Value in Stored Procedures in SQL Servers.

There are two ways of returning result sets or data from a Stored Procedure to a calling program, output parameters and return value.

Returning Data Using Output Parameter

If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure.

Returning Data Using Return Value

A procedure can return an integer value called a return value to indicate the execution status of a procedure. You specify the return value for a procedure using the RETURN statement. As with OUTPUT parameters, you must save the return code in a variable when the procedure is executed to use the return value in the calling program.

Let’s try a practical approach.

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'

Output

Note, An output parameter in a Stored Procedure is used to return any value.

With only one return value

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'

Output

Note, Generally, a return value is used to convey success or failure.

Here, the @@ERROR function indicates whether an error occurred during the execution of the statement. 0 (zero) indicates success, and any non-zero value indicates failure.

With multiple output parameters with the same data type.

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.