User's Guide
   PART 1. Working with Databases
     CHAPTER 10. Using Procedures, Triggers, and Batches       

Introduction to procedures

In order to use procedures, you need to understand how to do the following:

• Call procedures from a database application

• Create procedures

• Drop, or remove, procedures

• Control who has permission to use procedures

This section discusses each of these aspects of using procedures, and also describes some of the different uses of procedures.

  Creating procedures

Procedures are created using the CREATE PROCEDURE statement. You must have RESOURCE authority in order to create a procedure.

Where you enter the statement depends on the tool you are using:

• You can create the example procedure new_dept by connecting to the sample database from Interactive SQL as user ID DBA, using password SQL, and typing the statement in the command window.

• You can create the example procedure by connecting to the sample database from Sybase Central, opening the Procedures folder, and clicking Add Procedure/Function Wizard. The Wizard walks you through the process. Alternatively, click Add Procedure/Function Template, which places you immediately in the last window of the Wizard, the Procedure window, in which you enter the code for the procedure.

• If you are using a tool other than Interactive SQL or Sybase Central, follow the instructions for your tool. You may need to change the command delimiter away from the semicolon before entering the CREATE PROCEDURE statement.

The following simple example creates a procedure that carries out an INSERT into the department table of the sample database, creating a new department.

CREATE PROCEDURE new_dept ( IN id INT,
IN name CHAR(35),
IN head_id INT )
BEGIN
INSERT 
INTO dba.department ( dept_id,
dept_name,
dept_head_id )
VALUES ( id, name, head_id );
END

     For a complete description of the CREATE PROCEDURE syntax, see CREATE PROCEDURE statement.

The body of a procedure is a compound statement. The compound statement starts with a BEGIN statement and concludes with an END statement. In the case of new_dept, the compound statement is a single INSERT bracketed by BEGIN and END statements.

     For more information, see Using compound statements.

Parameters to procedures are marked as one of IN, OUT, or INOUT. All parameters to the new_dept procedure are IN parameters, as they are not changed by the procedure.

  Calling procedures

A procedure is invoked with a CALL statement. Procedures can be called by an application program, or they can be called by other procedures and triggers.

     For more information, see CALL statement.

The following statement calls the new_dept procedure to insert an Eastern Sales department:

CALL new_dept( 210, 'Eastern Sales', 902 );

After this call, you may wish to check the department table to see that the new department has been added.

The new_dept procedure can be called by all users who have been granted EXECUTE permission for the procedure, even if they have no permissions on the department table.

  Dropping procedures

Once a procedure is created, it remains in the database until it is explicitly removed. Only the owner of the procedure or a user with DBA authority can drop the procedure from the database.

The following statement removes the procedure new_dept from the database:

DROP PROCEDURE new_dept

  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.

  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 Interactive SQL, carry out the following steps:

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

2. Create the procedure.

3. Create 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)

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

   CALL AverageSalary(Average) 

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

5. Look at the value of the output variable Average. The Interactive SQL 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 Interactive SQL, 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 Interactive SQL, you can CALL it, specifying one of the departments of the company. The results are displayed in the Interactive SQL Data window. For example:

  To list the salaries of employees in the R & D department (department ID 100):

1. Type the following:

CALL SalaryList (100)

Employee ID	Salary
102	45700.000
105	62000.000
160	57490.000
243	72995.000
247	48023.690

To execute a CALL of a procedure that returns a result set, Interactive SQL opens a cursor.

The cursor is left open after the CALL in case a second result set is returned. The Interactive SQL statistics window displays the plan of the SELECT query in the procedure and then displays the line:

Procedure is executing. Use RESUME to continue.

You need to execute the RESUME statement or the Interactive SQL CLEAR command from the Interactive SQL Command window before you can alter or drop the procedure.

     For more information about using cursors in procedures, see Using cursors in procedures and triggers.