Rishul Matta Rishul Matta - 5 months ago 15
SQL Question

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

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

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.