What is the @return_value of stored procedure

I think you need to revisit the stored procedure logic, which you have written. You need to be very clear on what you are returning.

Below are some of the best practices:

• Use RETURN for only returning execution status of the procedure execution. Don’t return any value, other than the procedure execution status.
• For returning scalar values from procedure, use OUTPUT parameter
• For returning multiple values/resultset from procedure, use SELECT statement.

The "return value" of a stored procedure is just whatever value you return inside the stored procedure code using the return statement. If you don't have an explicit return statement, then the procedure will return a value of zero.

Let's look at some examples. In the first example, I use an explicit return statement. I tell the procedure to return the value 1000:

create or alter procedure p as begin
    set nocount on;
    return 1000; -- whatever value I want it to be
end
go

declare @returnvalue int; 
exec @returnvalue = p;
print concat('return value was ',@returnvalue); -- prints "return value was 1000"
go

This time I have commented out the return statement. The procedure will return the default value of zero:

create or alter procedure p as begin
    set nocount on;
    -- return 1000; comment out the return -> no explicit return value
end
go

-- this code will print "return value was 0" because that's the default
declare @returnvalue int; 
exec @returnvalue = p;
print concat('return value was ', @returnvalue); 
go

We can use the return value however we like, but by convention return values are used to provide information about the success or failure of the procedure. You could use it to return "data", but for that you should use output parameters instead.

In the example below you can see a pretty standard pattern. If no error occurs, we will get to the bottom of the try block, and hit the return 0 statement. Why zero? It's just a convention dating back decades, which means "nothing went wrong, there is no meaningful error to report".

But in the code below I deliberately do something that will cause an error. This means we won't get to the return 0 statement at the end of the try block.

Instead, execution will jump to the catch block. Inside the catch block I return the value of the error_number() associated with the error I deliberately caused.

create or alter procedure p as begin
    set nocount on;
    begin try
        -- deliberately do something that will cause an error
        declare @i int = 'abc';
        return 0; -- by pure convention, a return value of 0 means no error.
    end try begin catch
        print error_message();
        return error_number(); -- will be 245 because that's the error number of a conversion failure
    end catch
end
go

-- prints:
-- Conversion failed when converting the varchar value 'abc' to data type int.
-- return value was 245
declare @returnvalue int; 
exec @returnvalue = p;
print concat('return value was ', @returnvalue); 
go