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.
Creating a new procedure (Sybase Central)
Connect to a database with DBA or Resource authority.
Open the Procedures folder of the database.
In the right pane, double-click Add Procedure (Wizard).
Follow the instructions in the wizard.
When the Code Editor opens, complete the code of the procedure.
To execute the code in the database, choose File > Save/Execute in Database.
The new procedure appears in the Procedures folder.
Launch Interactive SQL and connect to a database using DBA authority.
Type the commands for the procedure in the SQL Statements pane of the Interactive SQL viewer.
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.
To create a remote procedure in IQ, you must use the AT location-string SQL
syntax of CREATE PROCEDURE to create a proxy
stored procedure. This capability is currently certified on Windows
and Sun Solaris only. For more information, see “Using remote procedure calls (RPCs)”. The Add
Remote Procedure Wizard in Sybase Central does not support remote
IQ procedures.
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
For a complete description of the CREATE PROCEDURE syntax, see Chapter 6, “SQL Statements,” of the Sybase IQ Reference Manual.
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.
