ASA SQL User's Guide
  Using Procedures, Triggers, and Batches
    Introduction to procedures

Creating procedures

Adaptive Server Anywhere provides a number of tools that let you create a new procedure.

In Sybase Central, you can use a wizard to provide necessary information. The Procedure Creation wizard also provides the option of using procedure templates.

Alternatively, you use the CREATE PROCEDURE statement to create procedures. However, you must have RESOURCE authority. Where you enter the statement depends on which tool you use.

To create a new procedure (Sybase Central)

1. Connect to a database as a user with DBA or Resource authority.

2. Open the Procedures & Functions folder of the database.

3. From the File menu, choose New > Procedure.

   The Procedure Creation wizard appears.

4. Follow the instructions in the wizard.

5. When the wizard finishes, you can complete the code of the procedure on the SQL tab in the right pane.

   The new procedure appears in the Procedures & Functions folder.

To create a new remote procedure (Sybase Central)

1. Connect to a database as a user with DBA authority.

2. Open the Procedures & Functions folder of the database.

3. From the File menu, choose New > Remote Procedure.

   The Remote Procedure Creation wizard appears.

4. Follow the instructions in the wizard.

5. When the wizard finishes, you can complete the code on the SQL tab in the right pane.

   The new remote procedure appears in the Procedures and Functions folder.

To create a procedure using a different tool

• Follow the instructions for your tool. You may need to change the command delimiter away from the semicolon before entering the CREATE PROCEDURE statement.

For more information about connecting, see Connecting to a Database.

Example 

The following simple example creates the procedure new_dept, which 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

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.

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.

For more information, see CREATE PROCEDURE statement, ALTER PROCEDURE statement, and Using compound statements.