In Sybase Central, the Create Procedure Wizard provides the option of using procedure templates. Alternatively, you can use Interactive SQL to execute a CREATE PROCEDURE statement to create a procedure. You must have DBA or RESOURCE authority to create procedure.

To create a new procedure (Sybase Central)

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

2. In the left pane, double-click Procedures & Functions.

3. Choose File » New » Procedure.

4. Follow the instructions in the Create Procedure Wizard.

5. In the right pane, click the SQL tab to complete the procedure code.

   The new procedure appears in Procedures & Functions.

Example

The following simple example creates the procedure NewDepartment, which performs an INSERT into the Departments table of the SQL Anywhere sample database, creating a new department.

CREATE PROCEDURE NewDepartment(
   IN id INT,
   IN name CHAR(35),
   IN head_id INT )
BEGIN
   INSERT
   INTO Departments ( DepartmentID,
       DepartmentName, DepartmentHeadID )
   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 NewDepartment, the compound statement is a single INSERT bracketed by BEGIN and END statements.

Parameters to procedures can be marked as one of IN, OUT, or INOUT. By default, parameters are INOUT parameters. All parameters to the NewDepartment procedure are IN parameters, as they are not changed by the procedure. You should set parameters to IN if they are not used to return values to the caller.

Temporary procedures

To create a temporary procedure, you must use the CREATE TEMPORARY PROCEDURE statement, an extension of the CREATE PROCEDURE statement. Temporary procedures are not permanently stored in the database. Instead, they are dropped at the end of a connection, or when specifically dropped, whichever occurs first. See CREATE PROCEDURE statement (web services).

Remote procedures

To create a remote procedure, you must have at least one remote server. See:

• Create remote procedures
• Create remote servers using Sybase Central

See also

• SQL Anywhere database connections
• CREATE PROCEDURE statement (web services)
• ALTER PROCEDURE statement
• Using compound statements
• Create remote procedures