Returning result sets from Transact-SQL procedures

Adaptive Server Anywhere and IQ use 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.

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

The following is the corresponding Adaptive Server Anywhere or IQ 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

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. The user must enable this functionality in the Option menu, make it a permanent change, and then restart or reconnect to DBISQL.

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