ASA SQL User's Guide
  Transact-SQL Compatibility

Returning result sets from Transact-SQL procedures

Adaptive Server Anywhere uses a RESULT clause to specify returned result sets. In Transact-SQL procedures, the column names or alias names of the first query are returned to the calling environment.

Example of Transact-SQL procedure 

The following Transact-SQL procedure illustrates how Transact-SQL stored procedures returns result sets:

CREATE PROCEDURE showdept (@deptname varchar(30))
AS
   SELECT employee.emp_lname, employee.emp_fname
   FROM department, employee
   WHERE department.dept_name = @deptname
   AND department.dept_id = employee.dept_id

Example of Watcom-SQL procedure 

The following is the corresponding Adaptive Server Anywhere procedure:

CREATE PROCEDURE showdept(in deptname varchar(30))
RESULT ( lastname char(20), firstname char(20))
BEGIN
   SELECT employee.emp_lname, employee.emp_fname
   FROM department, employee
   WHERE department.dept_name = deptname
   AND department.dept_id = employee.dept_id
END

For more information about procedures and results, see Returning results from procedures