User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To create a new procedure in the database.

Syntax

     CREATE PROCEDURE [ owner.]procedure-name ( [ parameter , ... ] )
          ...    {
              [ RESULT ( result-column , ... ) ] [ ON EXCEPTION RESUME ]
          ...     compound-statement
              | EXTERNAL NAME library-call
              }

Parameters

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

     parameter_mode:
          IN| OUT| INOUT

     result-column:
          column-name data-type

     library-call:
          '[operating-system:]function-name@library.dll; ...'

     operating-system:
          OS2
          | Windows3X
          | Windows95
          | WindowsNT
          | NetWare

Usage

Anywhere.

Permissions

Must have RESOURCE authority.

For external procedures, must have DBA authority.

Side effects

Automatic commit.

See also

• DROP statement
• CALL statement
• Compound statements
• GRANT statement
• CREATE FUNCTION statement
• EXECUTE IMMEDIATE statement
• The chapter "Using Procedures, Triggers, and Batches"

Description

The CREATE PROCEDURE statement creates (stores) a procedure in the database. A procedure can be created for another user by specifying a owner. A procedure is invoked with a CALL statement

  The body of a procedure consists of a compound statement. For information about compound statements, see "Compound statements".

Parameter names must conform to the rules for other database identifiers such as column names. They must be one of the types supported by SQL Anywhere (see "SQL Anywhere Data Types".), and must be prefixed by one of the keywords IN, OUT or INOUT. The keywords have the following meanings:

• IN argument is an expression that provides a value to the procedure.
• OUT argument is a variable that could be given a value by the procedure.
• INOUT argument is a variable that provides a value to the procedure, and could be given a new value by the procedure.

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 no default value is supplied, the parameter is NULL. SQLSTATE and SQLCODE are special parameters that output the SQLSTATE or SQLCODE value when the procedure ends (they are OUT parameters). Whether or not a SQLSTATE and SQLCODE parameter is specified, the SQLSTATE and SQLCODE special constants can always be checked immediately after a procedure call to test the return status of the procedure. However, the SQLSTATE and SQLCODE special constant 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.

Result sets

A procedure that returns result sets ("Returning results from procedures") may have a RESULT clause. The parenthesized list following the RESULT keyword defines the number of result columns and name and type. This information is returned by the Embedded SQL DESCRIBE or by ODBC SQLDescribeColwhen a CALL statement is being described. Allowable data types are listed in "SQL Anywhere Data Types".

Some procedures can return different result sets, 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 emp_fname
             FROM employee
         ELSE
             SELECT emp_lname,emp_fname
             FROM employee
         END IF
     END

These variable result set procedures 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, in order to get the proper shape of result set.
• ODBC Variable result set procedures can be used by ODBC applications. The proper description of the variable result sets is carried out by the ODBC driver.
• Open Client applications Variable result set procedures can be used by Open Client applications using the Open Server Gateway. The proper description of the variable result sets is carried out by the Open Server Gateway.
• ISQL ISQL does not support calling of variable result set procedures, and so cannot be used for testing this feature.

If your procedure does not return variable result sets, 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.

External procedures

A procedure using the EXTERNAL NAME clause is a wrapper around a call to an external dynamic link library, and is called an external stored procedure. An external stored procedure can have no clauses other than the EXTERNAL NAME clause following the parameter list. For a description of external procedures, see "Calling external libraries from stored procedures".

Example

• 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 "DBA"."product"
           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 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 company_name, CAST(     sum(sales_order_items.quantity *
           product.unit_price) AS INTEGER) VALUE
           FROM customer
           LEFT OUTER JOIN sales_order
           LEFT OUTER JOIN sales_order_items
           LEFT OUTER JOIN product
           GROUP BY company_name ;
       
           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