Which keyword is used to return value of a variable from 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 ParameterIf 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.

1. create procedure Out_test1 (@inValue int, @OutValue int output)  
2. as  
3.   
4. begin  
5.   
6. set @OutValue = @InValue  
7.   
8. end  
9.   
10. declare @x int ;  
11.   
12. exec Out_test1 @inValue=3, @OutValue = @x output  
13.   
14. 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.

1. create procedure Return_test1(@username varchar(20))  
2. as  
3.   
4. begin  
5.   
6. declare @returnvalue int  
7.   
8. insert into testUser(UserName) values(@username)  
9.   
10. set @returnvalue=@@ERROR  
11.   
12. end  
13.   
14. declare @x int  
15.   
16. exec @x=Return_test1 'aaa'  
17.   
18. 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.

1. create procedure Out_test2 (@inValue int, @OutValue1 int output,@OutValue2 int output)  
2.   
3. as  
4.   
5. begin  
6.   
7. set @OutValue1 = @InValue  
8.   
9. set @OutValue2=@inValue  
10.   
11. end  
12.   
13. declare @x int,@y int ;  
14.   
15. exec Out_test2 @inValue=3, @OutValue1 = @x output,@OutValue2=@y output  
16.   
17. 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.

1. create procedure Return_test2(@inValue1 int,@inValue2 int)  
2. as  
3.   
4.            begin  
5.   
6. return @inValue1  
7.   
8. return @inValue2  
9.   
10. end  
11.   
12. declare @x int,@y int  
13.   
14. exec @x=Return_test2 @inValue1=1,@inValue2=2  
15.   
16. 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.

1. create procedure Out_test3 (@OutValue1 int output,@OutValue2 datetime output,@outValue3 varchar(10) output)  
2.   
3. as  
4.   
5. begin  
6.   
7. set @OutValue1 = 10  
8.   
9. set @OutValue2=GETDATE()  
10.   
11. set @outValue3='test'  
12.   
13. end  
14.   
15. declare @x int,@y datetime,@z varchar(10);  
16.   
17. exec Out_test3 @OutValue1=@x output,@OutValue2=@y  
18.   
19. output,@outValue3=@z output  
20.   
21. 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.

1. create procedure Return_test3(@inValue varchar(10))  
2.   
3. as  
4.   
5. begin  
6. return @inValue  
7.   
8. end  
9.   
10. declare @x varchar(10)  
11.   
12. exec @x=Return_test3 @inValue='test'  
13.   
14. 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. 

How can we return a value in stored procedure?

What is return in stored procedure?

Which parameter used to return values in procedure?

How do you return a stored procedure in SQL Server?