I know that writing :
SELECT * FROM <ANY TABLE>
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.