What is the use of an OUTPUT parameter in SQL?-CodePudding

In an SQL Stored Procedure/Function, we can return multiple values. Then what is the use of an OUTPUT parameter? I wonder why they have introduced it? Can anyone explain?

CodePudding user response：

OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE or DELETE statement.

CodePudding user response：

If you are referring to RDBMS like SQL Server then output parameters are used to capture the result set of execution in a scalar variable.

Example:

Create procedure dbo.sp_square @inputparam int
as
declare @outputparam int
select @outputparam = @inputparam * @inputparam 
Return @outputparam

Execution to get and store the output value of a sp

Declare @result int
exec @result = dbo.sp_square 5

select @result

Result

If you are not returning the output in the stored procedure you will still get the result after the execution but you cannot store it in a variable or use it later in your code.