Result Sets from Transact-SQL Procedures

SQL Anywhere/Sybase IQ procedures and Transact-SQL procedures return result sets differently.

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.
Interactive SQL 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 Interactive SQL.
Interactive SQL Classic (deprecated) provides RESUME to display each successive result set.