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

Question

I know that writing :

    SELECT * FROM <ANY TABLE>

in a stored procedure will output a result set... what why do we have a return value separately in a stored procedure? where do we use it ? If any error comes then the result set will be null rite?

Answer 1

why do we have a return value separately in a stored procedure?

A stored procedure may return 0 or more resultsets. Insert, update, and delete normally don't produce a resultset, and a stored procedure may call select many times. In all cases, the resultset is data.

I suggest the best way to think of the "return value" is as status information: it indicates how the stored procedure worked out. You could return @@rowcount for an update. Sometimes it can be something simple, like the number of rows meeting some criteria, saving you the work of binding a variable to a single row to get the same answer. Or you could return 0 for success and nonzero for error; it's often easier to check the return status inline than in an error handler.

There's an analogy on the lines of the Unix cat utility that might help: it produces data on standard output, and returns an exit status to let the caller know whether or not it succeeded.

Answer 2

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.

Answer 3

You can use return value to return additional information from a stored procedure. This can be error codes, validation results or any other custom information you may want to return. It gives you additional flexibility when coding stored procedures.

Answer 4

Because if you use return values you can have a more fine grained control over the execution status and what the error (if any) were and you can return different error codes for malformed or invalid parameters etc and hence add error control/checking on the calling side to.

If you just check for an empty result set you really don't know why the set might be empty (maybe you called the procedure with an invalid parameter).

The main difference between a result set and a return value is that the result set stores the data returned (if any) and the return code holds some kind of status information about the execution itself.