CREATE PROCEDURE statement

Creates a user-defined SQL procedure in the database.

Syntax

CREATE [ OR REPLACE | TEMPORARY ] PROCEDURE [ owner.]procedure-name 
( [ parameter, ... ] )
[ RESULT ( result-column, ... ) | NO RESULT SET ]
[ SQL SECURITY { INVOKER | DEFINER } ]
[ ON EXCEPTION RESUME ]
compound-statement | AT location-string

parameter :
parameter-mode parameter-name data-type [ DEFAULT expression ]
| SQLCODE
| SQLSTATE

parameter-mode :
IN 
| OUT 
| INOUT

result-column : column-name data-type

Parameters

OR REPLACE clause
Specifying CREATE OR REPLACE PROCEDURE creates a new procedure, or replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing privileges. An error is returned if you attempt to replace a procedure that is already in use.
TEMPORARY clause
Specifying CREATE TEMPORARY PROCEDURE means that the stored procedure is visible only by the connection that created it, and that it is automatically dropped when the connection is dropped. Temporary stored procedures can also be explicitly dropped. You cannot perform ALTER, GRANT, or REVOKE on them, and, unlike other stored procedures, temporary stored procedures are not recorded in the catalog or transaction log.

Temporary procedures execute with the privileges of their creator (current user), or specified owner. You can specify an owner for a temporary procedure when:
- the temporary procedure is created within a permanent stored procedure
- the owner of the temporary and permanent procedure is the same
To drop the owner of a temporary procedure, you must drop the temporary procedure first.

Temporary stored procedures can be created and dropped when connected to a read-only database, and they cannot be external procedures.

For example, the following temporary procedure drops the fictitious table called CustRank. For this example, the procedure assumes that the table name is unique and can be referenced by the procedure creator without specifying the table owner:
```
CREATE TEMPORARY PROCEDURE drop_table( IN @TableName char(128) )
BEGIN
    IF EXISTS  ( SELECT * FROM SYS.SYSTAB WHERE table_name = @TableName ) THEN
       EXECUTE IMMEDIATE 'DROP TABLE "' || @TableName || '"';
       MESSAGE 'Table "' || @TableName || '" dropped' to client;
    END IF;
END;
CALL drop_table( 'CustRank' );
```

parameter
Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid SQL data type.

Parameters can be prefixed with one of the keywords IN, OUT, or INOUT. If you do not specify one of these values, parameters are INOUT by default. The keywords have the following meanings:
- IN
  The parameter is an expression that provides a value to the procedure.
- OUT
  The parameter is a variable that could be given a value by the procedure.
- INOUT
  The parameter is a variable that provides a value to the procedure, and could be given a new value by the procedure.
Set the data type explicitly, or specify the %TYPE or %ROWTYPE attribute to set the data type to the data type of another object in the database. Use %TYPE to set it to the data type of a column in a table or view. Use %ROWTYPE to set the data type to a composite data type derived from a row in a table or view. However, defining the data type using a %ROWTYPE that is set to a table reference variable (TABLE REF (table-reference-variable) %ROWTYPE is not allowed.

When procedures are executed using the CALL statement, not all parameters need to be specified. If a default value is provided in the CREATE PROCEDURE statement, missing parameters are assigned the default values. If an argument is not provided in the CALL statement, and no default is set, an error is given.

SQLSTATE and SQLCODE are special OUT parameters that output the SQLSTATE or SQLCODE value when the procedure ends. The SQLSTATE and SQLCODE special values can be checked immediately after a procedure call to test the return status of the procedure.

The SQLSTATE and SQLCODE special values are modified by the next SQL statement. Providing SQLSTATE or SQLCODE as procedure arguments allows the return code to be stored in a variable.

Specifying CREATE OR REPLACE PROCEDURE creates a new procedure, or replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing privileges. You cannot use the OR REPLACE clause with temporary procedures. An error is returned if the procedure being replaced is already in use. Open cursors for a connection are closed when a CREATE OR REPLACE PROCEDURE statement is executed.
RESULT clause
The RESULT clause declares the number and type of columns in the result set. The parenthesized list following the RESULT keyword defines the result column names and types. This information is returned by the Embedded SQL DESCRIBE or by ODBC SQLDescribeCol when a CALL statement is being described.

You can define the data type of the columns in the result set using the %TYPE or %ROWTYPE attribute. Use %TYPE to set a column type to the data type of a column in a table or view. Use %ROWTYPE to set the data type to a composite data type derived from a row in a table or view.

If a RESULT clause is specified, it must be the first clause of the statement.

Some procedures can produce more than one result set, with different numbers of columns, depending on how they are executed. For example, the following procedure returns two columns under some circumstances, and one in others.
```
CREATE PROCEDURE names( IN formal char(1))
BEGIN
    IF formal = 'n' THEN
        SELECT GivenName
        FROM GROUPO.Employees
    ELSE
        SELECT Surname, GivenName
        FROM GROUPO.Employees
    END IF
END;
```
Procedures with variable result sets must be written without a RESULT clause, or in Transact-SQL. Their use is subject to the following limitations:
- Embedded SQL
  You must DESCRIBE the procedure call after the cursor for the result set is opened, but before any rows are returned, to get the proper shape of result set. The CURSOR cursor-name clause on the DESCRIBE statement is required.
- ODBC, OLE DB, ADO.NET
  Variable result-set procedures can be used by applications using these interfaces. The proper description of the result sets is carried out by the driver or provider.
- Open Client applications
  Variable result-set procedures can be used by Open Client applications.
- Web services
  Web services rely on the RESULTS clause of the stored procedure to determine the number and types of the column in the result set. Web services do not support procedures that return multiple result sets, nor do they support variable result sets through the use of EXECUTE IMMEDIATE.
Note If an EXECUTE IMMEDIATE statement that includes a WITH RESULT SET ON clause is used in the procedure, and if the result set that is returned from the statement is the same as the result set that is returned from the procedure, then only the first column of the EXECUTE IMMEDIATE statement's result set is returned.

If your procedure returns only one result set, you should use a RESULT clause. The presence of this clause prevents ODBC and Open Client applications from re-describing the result set after a cursor is open.

To handle multiple result sets, ODBC must describe the currently executing cursor, not the procedure's defined result set. Therefore, ODBC does not always describe column names as defined in the RESULT clause of the procedure definition. To avoid this problem, use column aliases in the SELECT statement that generates the result set.
NO RESULT SET clause
If a NO RESULT SET clause is specified, it must be the first clause of the statement.

Declares that no result set is returned by this procedure. This is useful when an external environment needs to know that a procedure does not return a result set.
SQL SECURITY clause
The SQL SECURITY clause defines whether the procedure is executed as the INVOKER (the user who is calling the procedure), or as the DEFINER (the user who owns the procedure). The default is DEFINER.

When SQL SECURITY INVOKER is specified, more memory is used because annotation must be done for each user that calls the procedure. When SQL SECURITY INVOKER is specified, name resolution is done as the invoker as well. Therefore, make sure to qualify all object names (tables, procedures, and so on) with their appropriate owner. For example, suppose user1 creates the following procedure:
```
CREATE PROCEDURE user1.myProcedure()
    RESULT( columnA INT )
    SQL SECURITY INVOKER
BEGIN
    SELECT columnA FROM table1;
END;
```
If user2 attempts to run this procedure and a table user2.table1 does not exist, a table lookup error results. Additionally, if a user2.table1 does exist, that table is used instead of the intended user1.table1. To prevent this situation, qualify the table reference in the statement (user1.table1, instead of just table1).
ON EXCEPTION RESUME clause
This clause enables Transact-SQL-like error handling to be used within a Watcom SQL syntax procedure.

If you use ON EXCEPTION RESUME, the procedure takes an action that depends on the setting of the on_tsql_error option. If on_tsql_error is set to Conditional (the default) the execution continues if the next statement handles the error; otherwise, it exits.

Error-handling statements include the following:
- IF
- SELECT @variable =
- CASE
- LOOP
- LEAVE
- CONTINUE
- CALL
- EXECUTE
- SIGNAL
- RESIGNAL
- DECLARE
- SET VARIABLE
You should not use explicit error handling code with an ON EXCEPTION RESUME clause.

This clause is ignored within the TRY block of a BEGIN...END statement.
compound-statement
A set of SQL statements bracketed by BEGIN and END, and separated by semicolons.
AT clause
Create a proxy stored procedure on the current database for a remote procedure specified by location-string. The AT clause supports the semicolon (;) as a field delimiter in location-string. If no semicolon is present, a period is the field delimiter. This allows file names and extensions to be used in the database and owner fields.

The string in the AT clause can also contain local or global variable names enclosed in braces ({variable-name}). The SQL variable name must be of type CHAR, VARCHAR, or LONG VARCHAR. For example, an AT clause that contains 'bostonase.master.dbo.{@myprocedure}' indicates that @myprocedure is a SQL variable and that the current contents of the @myprocedure variable should be substituted when the remote procedure is used.

If a remote procedure can return a result set, even if it does not always return one, then the local procedure definition must contain a RESULT clause.

Remarks

The CREATE PROCEDURE statement creates a procedure in the database. A procedure is invoked with a CALL statement.

You can create permanent stored procedures that call external or native procedures written in a variety of programming languages.

You can use PROC as a synonym for PROCEDURE.

When referencing a temporary table from multiple procedures, a potential issue can arise if the temporary table definitions are inconsistent and statements referencing the table are cached.

The body of a procedure is a compound statement. The compound statement starts with a BEGIN statement and concludes with an END statement. For 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.

Privileges

You must have the CREATE PROCEDURE system privilege to create procedures owned by you.

You must have the CREATE ANY PROCEDURE or CREATE ANY OBJECT privilege to create procedures owned by others.

You do not need any privilege to create temporary procedures.

To replace an existing procedure, you must own the procedure or have one of the following:

CREATE ANY PROCEDURE and DROP ANY PROCEDURE system privileges.
CREATE ANY OBJECT and DROP ANY OBJECT system privileges.
ALTER ANY OBJECT or ALTER ANY PROCEDURE system privileges.

Side effects

Automatic commit, even for temporary procedures.

Standards

ANSI/ISO SQL Standard
CREATE PROCEDURE is a core feature of the ANSI/ISO SQL Standard, but some of its components supported in SQL Anywhere are optional SQL language features. A subset of these features includes:
- The SQL SECURITY clause is optional ANSI/ISO SQL Language Feature T324.
- The ability to pass a LONG VARCHAR, LONG NVARCHAR, or LONG BINARY value to a SQL procedure is ANSI/ISO SQL Language Feature T041.
- The ability to create or modify a schema object within a SQL procedure, using statements such as CREATE TABLE or DROP TRIGGER, is ANSI/ISO SQL Language Feature T651.
- The ability to use a dynamic-SQL statement within a SQL procedure, including statements such as EXECUTE IMMEDIATE, PREPARE, and DESCRIBE, is ANSI/ISO SQL Language Feature T652.
Several clauses of the CREATE PROCEDURE statement are not in the standard. These include:
- The TEMPORARY clause.
- The ON EXCEPTION RESUME clause.
- The AT clause.
- The optional DEFAULT clause for a specific routine parameter.
- The RESULT and NO RESULT SET clauses. The ANSI/ISO SQL Standard uses the RETURNS keyword.
- The optional OR REPLACE clause.
Transact-SQL
CREATE PROCEDURE is supported by Adaptive Server Enterprise.

Example

The following procedure queries the Employees table and returns salaries that are within the specified percent (percentage) of a specified salary (sal):

CREATE OR REPLACE PROCEDURE AverageEmployees( IN percentage NUMERIC( 5,3), IN sal NUMERIC( 20, 3 ) )
RESULT( Department CHAR(40), GivenName person_name_t, Surname person_name_t, Salary NUMERIC( 20, 3) )
BEGIN
    DECLARE maxS NUMERIC( 20, 3 );
    DECLARE minS NUMERIC( 20, 3 );

    IF percentage >= 1 THEN
        SET percentage = percentage / 100;
    ELSEIF percentage < 0 THEN
        SELECT 'Percentage error', 'Err','Err', -1;
        RETURN;
    END IF;

    SELECT MIN( E.Salary ), MAX( E.Salary ) INTO minS, maxS
    FROM GROUPO.Employees E;

    IF sal < minS OR sal > maxS THEN
        SELECT 'Salary out of bounds', 'Err', 'Err', -2;
        RETURN;
    END IF;

    SELECT D.DepartmentName, E.GivenName, E.Surname, E.Salary
    FROM GROUPO.Employees E JOIN Departments D ON E.DepartmentID = D.DepartmentID
    WHERE E.Salary BETWEEN sal *( 1 - percentage ) AND sal * ( 1 + percentage );
END;

The following procedure uses a CASE statement to classify the results of a query:

CREATE PROCEDURE ProductType (IN product_ID INT, OUT type CHAR(10))
BEGIN
    DECLARE prod_name CHAR(20);
    SELECT name INTO prod_name FROM GROUPO.Products
    WHERE ID = product_ID;
    CASE prod_name
    WHEN 'Tee Shirt' THEN
        SET type = 'Shirt'
    WHEN 'Sweatshirt' THEN
        SET type = 'Shirt'
    WHEN 'Baseball Cap' THEN
        SET type = 'Hat'
    WHEN 'Visor' THEN
        SET type = 'Hat'
    WHEN 'Shorts' THEN
        SET type = 'Shorts'
    ELSE
        SET type = 'UNKNOWN'
    END CASE;
END;

The following example replaces the ProductType procedure created in the previous example. After replacing the procedure, the parameters for Tee Shirt and Sweatshirt are updated:

CREATE OR REPLACE PROCEDURE ProductType (IN product_ID INT, OUT type CHAR(10))
BEGIN
    DECLARE prod_name CHAR(20);
    SELECT name INTO prod_name FROM GROUPO.Products
    WHERE ID = product_ID;
    CASE prod_name
    WHEN 'Tee Shirt' THEN
        SET type = 'T Shirt'
    WHEN 'Sweatshirt' THEN
        SET type = 'Long Sleeve Shirt'
    WHEN 'Baseball Cap' THEN
        SET type = 'Hat'
    WHEN 'Visor' THEN
        SET type = 'Hat'
    WHEN 'Shorts' THEN
        SET type = 'Shorts'
    ELSE
        SET type = 'UNKNOWN'
    END CASE;
END;

The following procedure uses a cursor and loops over the rows of the cursor to return a single value:

CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
    DECLARE err_notfound EXCEPTION
    FOR SQLSTATE '02000';
    DECLARE curThisCust CURSOR FOR
        SELECT CompanyName,
             CAST(SUM(SalesOrderItems.Quantity *
             Products.UnitPrice) AS INTEGER) VALUE
        FROM GROUPO.Customers
        LEFT OUTER JOIN SalesOrders
        LEFT OUTER JOIN SalesOrderItems
        LEFT OUTER JOIN Products
        GROUP BY CompanyName;
    DECLARE ThisValue INT;
    DECLARE ThisCompany CHAR(35);
    SET TopValue = 0;
    OPEN curThisCust;
    CustomerLoop:
    LOOP
        FETCH NEXT curThisCust
        INTO ThisCompany, ThisValue;
        IF SQLSTATE = err_notfound THEN
            LEAVE CustomerLoop;
        END IF;
        IF ThisValue > TopValue THEN
            SET TopValue = ThisValue;
            SET TopCompany = ThisCompany;
            END IF;
    END LOOP CustomerLoop;
    CLOSE curThisCust;
END;

The following 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 GROUPO.Departments ( DepartmentID,
       DepartmentName, DepartmentHeadID )
   VALUES ( id, name, head_id );
END;

The following statement creates a procedure, DepartmentsCloseToCustomerLocation, and sets its IN parameter to the data type of the ID column in the Customers table using a %TYPE attribute:

CREATE OR REPLACE PROCEDURE DepartmentsCloseToCustomerLocation( IN customer_ID Customers.ID%TYPE )
  BEGIN
    DECLARE cust_rec Customers%ROWTYPE;

    SELECT City, State, Country
    INTO cust_rec.City, cust_rec.State, cust_rec.Country
    FROM Customers
    WHERE ID = customer_ID;

    SELECT Employees.Surname, Employees.GivenName, Departments.DepartmentName
    FROM Employees JOIN Departments
    ON Departments.DepartmentHeadID = Employees.EmployeeID
    WHERE Employees.City = cust_rec.City
    AND Employees.State = cust_rec.State
    AND Employees.Country = cust_rec.Country;
  END;

CALL DepartmentsCloseToCustomerLocation(158);