Sybase 12.4.2 manual Returning procedure results in result sets, 234, Employee ID Salary

Introduction to procedures

3Create a variable to hold the procedure output. In this case, the output variable is numeric, with three decimal places, so create a variable as follows:

CREATE VARIABLE Average NUMERIC(20,3)

4Call the procedure, using the created variable to hold the result:

CALL AverageSalary(Average)

The DBISQL statistics window displays the message "Procedure completed" if the procedure was created and run properly.

Look at the value of the output variable Average. The DBISQL Data window displays the value 49988.623 for this variable, the average employee salary.

Returning procedure results in result sets

In addition to returning results to the calling environment in individual parameters, procedures can return information in result sets. A result set is typically the result of a query. The following procedure returns a result set containing the salary for each employee in a given department:

CREATE PROCEDURE SalaryList (IN department_id INT)

RESULT ( "Employee ID" INT, "Salary" NUMERIC(20,3) )

BEGIN

SELECT emp_id, salary

FROM employee

WHERE employee.dept_id = department_id;

END

If this procedure is called from DBISQL, the names in the RESULT clause are matched to the results of the query and used as column headings in the displayed results.

To test this procedure from DBISQL, you can CALL it, specifying one of the departments of the company. The results are displayed in the DBISQL Data window. For example, to list the salaries of employees in the R & D department (department ID 100), type the following:

CALL SalaryList (100)

Employee ID	Salary
102	45700.000
105	62000.000
160	57490.000

234