Returning result sets from procedures

Result sets allow a procedure to return more than one row of results to the calling environment.

The following procedure returns a list of customers who have placed orders, together with the total value of the orders placed. The procedure does not list customers who have not placed orders. 

CREATE PROCEDURE ListCustomerValue()
RESULT ("Company" CHAR(36), "Value" INT)
BEGIN
   SELECT CompanyName,
      CAST( sum(  SalesOrderItems.Quantity *
                  Products.UnitPrice)
                  AS INTEGER ) AS value
   FROM Customers
      INNER JOIN SalesOrders
      INNER JOIN SalesOrderItems
      INNER JOIN Products
   GROUP BY CompanyName
   ORDER BY value DESC;
END;

  • Type the following:

    CALL ListCustomerValue ( );
Company Value
The Hat Company 5016
The Igloo 3564
The Ultimate 3348
North Land Trading 3144
Molly's 2808
... ...
Notes

  • The number of variables in the RESULT list must match the number of the SELECT list items. Automatic data type conversion is performed where possible if data types do not match.

  • The RESULT clause is part of the CREATE PROCEDURE statement, and does not have a command delimiter.

  • The names of the SELECT list items do not need to match those of the RESULT list.

  • When testing this procedure, Interactive SQL displays only the first result set by default. You can configure Interactive SQL to display more than one result set by setting the Show Multiple Result Sets option on the Results tab of the Options window.

  • You can modify procedure result sets, unless they are generated from a view. The user calling the procedure requires the appropriate permissions on the underlying table to modify procedure results. This is different than the usual permissions for procedure execution, where the procedure owner must have permissions on the table. See Editing result sets in Interactive SQL.

  • If a stored procedure or user-defined function returns a result set, it cannot also set output parameters or return a return value.

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1