First of all you have two distinct ways to return something. You may return a result set (i.e. a table) as the result of the operation as well as return value indicating either some sort of error or status of the result set.

Also, a return value is limited to a single 32bit integer, whereas a result set can have as many rows and columns the RDBMS allows.

My personal opinion is to use a stored procedure to execute a task mainly, and not to create a result set. But that is a matter of taste. However, using this paradigm, an action should inform the caller about the success and -in case of a failure- about the reason. Some RDBMS allow using exceptions, but if there is nothing to throw, i.e. just returning a status (e.g. 0,1,2 for 'data was new and had to be inserted, data existed and was updated, data could not be updated etc.)

There is a third way to pass information back to the caller: By using output parameter. So you have three different possibilities of passing information back to the caller.

This is one more than with a 'normal' programming language. They usually have the choice of either returning a value (e.g. int Foo() or an output/ref parameter void Foo(ref int bar). But SQL introduces a new and very powerful way of returning data (i.e. tables).

In fact, you may return more than one table which makes this feature even more powerful.

Email codedump link for What is the difference between a Result Set and Return value in a SQL procedure? what do they signify?