Returning Multiple Result Sets

  • Article

If you execute a stored procedure that contains native server syntax SELECT statements, each result set is returned to a separate Visual FoxPro cursor. You can use these cursors to return values or parameters from a server stored procedure to the Visual FoxPro client.

To return multiple result sets

  • Use the SQLEXEC( ) function to select multiple results sets using your native server syntax.

For example, the following code creates and executes a SQL server stored procedure, my_procedure, that returns three Visual FoxPro cursors: sqlresult, sqlresult1, and sqlresult2:

=SQLEXEC(nConnectionHandle,'create procedure my_procedure as ;
      select * from sales; select * from authors; 
      select * from titles')
=SQLEXEC(nConnectionHandle,'execute my_procedure')

How the Server Processes Result Sets and Errors

Because the server compiles each stored procedure when you create it, you receive any server syntax errors at create time. When you execute the stored procedure, the server executes the compiled SQL statements sequentially (as in a Visual FoxPro program) and Visual FoxPro fetches each result set from each SQL statement within the stored procedure separately, in the order executed.

Result sets and errors are returned in the order received, and processing stops if an error is encountered. For example, if a run-time error occurs when the server executes the third statement in a four-statement stored procedure, you receive the first two result sets and then receive the error that occurred upon processing the third result set. Processing stops after the error is returned; the fourth result set is not retrieved. You can use the AERROR( ) function to obtain information about the most recent error.

Note   You can execute server stored procedures from Visual FoxPro only by using Visual FoxPro SQL pass-through functions. Views don't support server stored procedures, because each view contains an explicit SQL SELECT statement in its SQL definition.

Passing a SQL Statement to the Data Source

The SQLEXEC( ) function enables you to send a SQL statement to the data source without interpretation. In the simplest case, any string you enclose in the second parameter of the SQLEXEC( ) function is passed to your data source without interpretation. This allows you to execute any statement using your data source's native SQL.

You can also use the SQLEXEC( ) function to create a parameterized query, or to pass ODBC extensions to SQL to the data source.

See Also

Accessing Server Stored Procedures with SQL Pass-Through Functions | Creating a Parameterized Query | Using SQL Pass-Through Technology | Implementing a Client/Server Application | Designing Client/Server Applications | Upsizing Visual FoxPro Databases | Creating Views