Can we return single value 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 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.

With output parameter.

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' 

With Return value

create procedure Return_test1(@username varchar(20)) 
as 
 
begin 
 
declare @returnvalue int 
 
insert into testUser(UserName) values(@username) 
 
set @returnvalue=@@ERROR 
Return @returnvalue;
 
end 
 
declare @x int 
 
exec @x=Return_test1 'aaa' 
 
select @x 'Return_value'