Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Stored procedures, triggers, batches, and user-defined functions » Result sets


Ways to return results as procedure parameters

Procedures can return results to the calling environment in the parameters to the procedure. Within a procedure, parameters and variables can be assigned values using:

  • the SET statement

    The following procedure returns a value in an OUT parameter assigned using a SET statement. You must have the CREATE PROCEDURE system privilege to execute the following statement:

    CREATE PROCEDURE greater( 
       IN a INT,
       IN b INT,
       OUT c INT )
       IF a > b THEN
          SET c = a;
          SET c = b;
       END IF ;
  • a SELECT statement with an INTO clause

    A single-row query retrieves at most one row from the database. This type of query uses a SELECT statement with an INTO clause. The INTO clause follows the SELECT list and precedes the FROM clause. It contains a list of variables to receive the value for each SELECT list item. There must be the same number of variables as there are SELECT list items.

    When a SELECT statement executes, the database server retrieves the results of the SELECT statement and places the results in the variables. If the query results contain more than one row, the database server returns an error. For queries returning more than one row, you must use cursors.

    If the query results in no rows being selected, the variables are not updated, and a warning is returned.

    You must have the appropriate SELECT privileges on the object to execute a SELECT statement.

 Example 1: Create a procedure and select its results using a SELECT...INTO statement
 Example 2: Returning the results of a single-row SELECT statement
 See also