Difference between Output Parameter and Return Value in Stored Procedure

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 with practical approach.

1. With only one output parameter.

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:

Point to Remember: An output parameter in a Stored Procedure is used to return any value.

2. 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:

Point to Remember: 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.

3. With multiple output parameter 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:

Point to Remember: An output parameter can return one or more values.

4. 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.

Point to Remember: A return value can return only one value.

5. Multiple output parameter with different data type.

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:

Point to Remember: An output parameter returns data with any data type.

6. Return value with different data type.

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.

Point to Remember: the return value returns data of only an integer data type.

The following is when to use output parameter 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 success or failure of the Stored Procedure.
• A return a value of 0 indicates success and any non-zero value indicates failure.  

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