Get Started

You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

Become a sharper developer and jumpstart your career.

Basic

$0

$

. 00

monthly

For Basic members:

  • Standard Profile
  • Access learning courses
  • Access free e-books
  • 1x point

Premium

$20

$

. 00

monthly

For Premium members:

  • Earn 2x reward points
  • Premium profile with Verified check
  • Premium Avatar with status update
  • Learning courses
  • Unlimited Certifications and badges on profile
  • Unlimited Book downloads
  • Access to Premium content
  • Online Training
  • Unlimited PDF conversions and downloads
  • Professional Resume Writing
  • Interview Preparations Guide
  • 25 messages per month
  • 1 "Kodzu" avatar, basic
SQL Language

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.

  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. 

Up Next
    Ebook Download
    View all
    Learn
    View all