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.

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

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

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

• ISQL displays all results in a single stream.

• DBISQL presents each result set on a separate tab. You must enable this functionality in the Option menu. Make it a permanent change, then restart or reconnect to DBISQL.

• DBISQLC provides the RESUME command to display each successive result set.