Working with result sets in stored procedures

We can get the results from a stored procedure either in the form of the OUT or INOUT parameters or as result sets. In this recipe, we will discuss returning result sets in stored procedures. A stored procedure can be invoked either from a client application or from another stored procedure. If we return a result set from a procedure, we need to decide where to return it. We can return the result set to its caller or to the client. If it's returned to CALLER, then the result sets are not available at the end client. In this recipe, we will discuss how we can return result sets from a stored procedure.

How to do it...

Returning a result set: To return a result set from a procedure, we need to declare a cursor with the required definition and leave the cursor OPEN. The cursor should be defined as WITH RETURN; otherwise, the result set will not be returned.

For example, if we want to return all the employees with a salary greater than 10,000, then...