Table of ContentsPreviousNext

Ispirer
Please, note, that Ispirer SQLWays 6.0 is no longer supported and provided to clients.
You can try out automated conversion of databases and applications with Ispirer Toolkit for free. Download free trial.
Check out the relevant toolkit documentation.
Ispirer Database Conversion overview.


Ispirer SQLWays Database Migration Software

Returning Result Set to Client

The set of rows returned by a query is called the result set. The result set can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria.

Returning result sets means that the client application will be able to retrieve and process rows returned by the procedure.

TABLE 73. Returning Result Sets from Procedure
Database
Syntax
Description
Microsoft SQL Server
create procedure proc_name
as
begin
select_statement
end 
Standalone SELECT statement in a Microsoft SQL Server procedure returns the result set to the client application.
 
For example, "SELECT * FROM Products" generates a default result set of all the products in the Products table. SQL Server sends this result set to the client.
IBM DB2
create procedure proc_name ()
LANGUAGE SQL
[DYNAMIC RESULT SETS N
begin
declare cursor_name CURSOR WITH     
RETURN [TO CALLER | TO CLIENT]
FOR select_statement;
open cur1;
end 
Cursor, which is declared with the WITH RETURN attribute and which isn't closed in the procedure body, returns the result set to the client.
 
WITH RETURN indicates that the cursor is intended for use as a result set from a stored procedure.
 
DYNAMIC RESULT SETS N - Indicates the estimated upper bound of returned result sets for the stored procedure.
 
Within an SQL procedure, cursors, declared using the WITH RETURN clause, are still open when the SQL procedure ends, define the result sets from the SQL procedure. All other open cursors in an SQL procedure are closed when the SQL procedure ends.
 
The default for all cursors is WITH RETURN TO CALLER.
TO CALLER - Specifies that the cursor can return a result set to the caller. For example, if the caller is another stored procedure, the result set is returned to that stored procedure. If the caller is a client application, the result set is returned to the client application.
TO CLIENT - Specifies that the cursor can return a result set to the client application. This cursor is invisible to any intermediate nested procedures.


Table of ContentsPreviousNext
Copyright 1999-2023 Ispirer Systems.
Ispirer and SQLWays are registered trademarks. All other product names may be trademarks of the respective companies.
All rights reserved.