Sybase 12.4.2 Permissions to execute procedures, Returning procedure results in parameters, 233

CHAPTER 6 Using Procedures and Batches

Permissions to execute procedures

A procedure is owned by the user who created it, and that user can execute it without permission. Permission to execute it can be granted to other users using the GRANT EXECUTE command.

For example, the owner of the procedure new_dept could allow another_user to execute new_dept with the statement:

GRANT EXECUTE ON new_dept TO another_user

The following statement revokes permission to execute the procedure:

REVOKE EXECUTE ON new_dept FROM another_user

For more information on managing user permissions on procedures, see “Granting permissions on procedures” on page 361.

Returning procedure results in parameters

Procedures can return results to the calling environment in one of the following ways:

•Individual values are returned as OUT or INOUT parameters.

•Result sets can be returned.

•A single result can be returned using a RETURN statement.

This section describes how to return results from procedures as parameters.

The following procedure on the sample database returns the average salary of employees as an OUT parameter.

CREATE PROCEDURE AverageSalary( OUT avgsal

NUMERIC (20,3) )

BEGIN

SELECT AVG( salary )

INTO avgsal

FROM employee;

END

To run this procedure and display its output from DBISQL, carry out the following steps:

1Connect to the sample database from DBISQL as user ID DBA using password SQL.

2Create the procedure.

233