Returning result sets from Transact-SQL procedures

SQL Anywhere and Sybase IQ use a RESULT clause to specify returned result sets. In Transact-SQL procedures, 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 return result sets:

CREATE PROCEDURE showdept (@deptname varchar(30))
AS
	SELECT Employees.Surname, Employees.GivenName
	FROM Departments, Employees
	WHERE Departments.DepartmentName = @deptname
	AND Departments.DepartmentID = Employees.DepartmentID

Example of Watcom-SQL procedure

The following is the corresponding SQL Anywhere or Sybase IQ procedure:

CREATE PROCEDURE showdept(in deptname varchar(30))
RESULT ( lastname char(20), firstname char(20))
BEGIN
	SELECT Employees.Surname, Employees.GivenName
	FROM Departments, Employees
	WHERE Departments.DepartmentName = deptname
	AND Departments.DepartmentID = Employee.DepartmentID
END

Multiple result sets

There are minor differences in the way the three Sybase client tools present multiple results to the client:

For more information about procedures and results, see Chapter 1, “Using Procedures and Batches” in the System Administration Guide: Volume 2.