User's Guide
   PART 5. The Adaptive Server Family
     CHAPTER 30. 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 result sets are returned from Transact-SQL stored procedures:

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))
ON EXCEPTION RESUME
BEGIN
   SELECT employee.emp_lname, employee.emp_fname
   FROM department, employee
   WHERE department.dept_name = deptname
   AND department.dept_id = employee.dept_id
END

Notes 

• Multiple result sets with a different number of columns or incompatible data types cannot be returned from procedures in Adaptive Server Anywhere.

• When a RESULT clause is not specified (as is the case with Transact-SQL procedures), Adaptive Server Anywhere determines the result set from the first SELECT statement in the procedure. The first SELECT statement is identified without regard for IF statements or other control statements: you cannot have a procedure return one result set under one set of conditions and an incompatible result set under other conditions.