|
SQL 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 Create Procedure wizard also provides the option of using procedure templates.
Alternatively, you can use Interactive SQL to execute a CREATE PROCEDURE statement to create a procedure. However, you must have RESOURCE authority.
Connect to the database as a user with DBA or Resource authority.
Open the Procedures & Functions folder of the database.
From the File menu, choose New > Procedure.
The Create Procedure wizard appears.
Follow the instructions in the wizard.
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.
For more information about connecting, see Connecting to a Database.
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.
For more information, see CREATE PROCEDURE statement, ALTER PROCEDURE statement, and Using compound statements.