Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (A-D)



Use this statement to create a procedure in the database.

Syntax 1 - Creating user defined procedures
CREATE [ TEMPORARY ] PROCEDURE [ owner.]procedure-name ( [ parameter, ... ] )
{   [ RESULT ( result-column, ... ) | NO RESULT SET ]
   | AT location-string
   | EXTERNAL NAME library-call
   | [ DYNAMIC RESULT SETS integer-expression ]
parameter :
  parameter-mode parameter-name data-type [ DEFAULT expression ]
parameter-mode : IN 
| OUT 
result-column : column-name data-type
library-call :
[operating-system:]function-name@library; ...
operating-system : Unix
java-call :
[package-name.]class-name.method-name method-signature
method-signature : 
( [ field-descriptor, ... ] ) return-descriptor
field-descriptor and return-descriptor :
| B 
| S 
| I 
| J 
| F 
| D 
| C 
| V 
| [descriptor 
| Lclass-name;
Syntax 2 - Create web services client procedure
CREATE PROCEDURE [ owner.]procedure-name ( [ parameter, ... ] )
URL url-string
[ HEADER header-string ]
[ SOAPHEADER soap-header-string ]
[ TYPE { 
  'HTTP[ :{ GET | POST[:MIME-type ] | PUT[:MIME-type ] | DELETE | HEAD } ]' | 
  'SOAP[:{ RPC | DOC } ]' } ]
[ NAMESPACE namespace-string ]
[ CERTIFICATE certificate-string ]
[ CLIENTPORT clientport-string ]
[ PROXY proxy-string ]
[ SET protocol-option-string
parameter :
  parameter-mode parameter-name data-type [ DEFAULT expression ]
parameter-mode : IN | OUT | INOUT
url-string :
{ HTTP | HTTPS | HTTPS_FIPS }://[user:password@]hostname[:port][/path]
header-string : string
[ http-option-list]
[, soap-option-list ]
http-option-list : 
[ CH[UNK]={ ON | OFF | AUTO } ]
[; VER[SION]={ 1.0 | 1.1 } ]
soap-operation-name : string
  • CREATE PROCEDURE clause   You can create permanent or temporary (TEMPORARY) stored procedures. You can use PROC as a synonym for PROCEDURE.

    Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid SQL data type. For a list of valid data types, see SQL data types.

    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.

    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 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 values can always 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 TEMPORARY (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 stored procedures execute with the permissions of their creator (current user), and can only be owned by their creator. Therefore, do not specify owner when creating a temporary stored procedure.

    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 table called CustRank, if it exists. 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) )
        IF EXISTS  ( SELECT * FROM SYS.SYSTAB WHERE table_name = @TableName ) THEN
       EXECUTE IMMEDIATE 'DROP TABLE "' || @TableName || '"';
       MESSAGE 'Table "' || @TableName || '" dropped' to client;
        END IF;
    CALL drop_table( 'CustRank' );

  • 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. Allowable data types are listed in SQL data types.

    For more information about returning result sets from procedures, see Returning results from procedures.

    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))
       IF formal = 'n' THEN
          SELECT GivenName
          FROM Employees
          SELECT Surname, GivenName
          FROM Employees
       END IF

    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.

    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   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. Also, when SQL SECURITY INVOKER is specified, name resolution is done as the invoker as well. Therefore, care should be taken 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 )
         SELECT columnA FROM table1;

    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, 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 (which is 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
    • CALL
    • SIGNAL

    You should not use explicit error handling code with an ON EXCEPTION RESUME clause.

    See on_tsql_error option [compatibility].

  • AT location-string 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.

    Remote procedures accept input parameters up to 254 bytes in length, and return up to 254 characters in output variables.

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

    For information about remote servers, see CREATE SERVER statement. For information about using remote procedures, see Using remote procedure calls (RPCs).

  • EXTERNAL NAME clause   A procedure using the EXTERNAL NAME clause is a wrapper around a call to an external library. A stored procedure using EXTERNAL NAME can have no other clauses following the parameter list. The library name may include the file extension, which is typically .dll on Windows and .so on Unix. In the absence of the extension, the software appends the platform-specific default file extension for libraries.

    Note that the EXTERNAL NAME clause is not supported for temporary functions.

    For information about external library calls, see Calling external libraries from procedures.

  • DYNAMIC RESULT SETS clause   This clause is directly tied to the EXTERNAL NAME LANGUAGE JAVA clause, and is for use with procedures that are wrappers around Java methods. If the DYNAMIC RESULT SETS clause is not provided, it is assumed that the method returns no result set.

  • EXTERNAL NAME java-call LANGUAGE JAVA clause   A procedure that uses EXTERNAL NAME with a LANGUAGE JAVA clause is a wrapper around a Java method. A Java method signature is a compact character representation of the types of the parameters and the type of the return value. If the number of parameters is less than the number indicated in the method-signature then the difference must equal the number specified in DYNAMIC RESULT SETS, and each parameter in the method signature in excess of those in the procedure parameter list must have a method signature of [Ljava/SQL/ResultSet;.

    The field-descriptor and return-descriptor have the following meanings:

    Field type Java data type
    B byte
    C char
    D double
    F float
    I int
    J long
    L class-name; an instance of the class class-name. The class name must be fully qualified, and any dot in the name must be replaced by a /. For example, java/lang/String
    S short
    V void
    Z Boolean
    [ use one for each dimension of an array

    For example,

    double some_method(
      boolean a,
      int b,
      java.math.BigDecimal c,
      byte [][] d,
      java.sql.ResultSet[] rs ) {

    would have the following signature:


    See Returning result sets from Java methods.

  • URL clause   For use only when defining an HTTP or SOAP web services client procedure. Specifies the URI of the web service. The optional user name and password parameters provide a means of supplying the credentials needed for HTTP basic authentication. HTTP basic authentication base-64 encodes the user and password information and passes it in the Authentication header of the HTTP request.

    Specifying HTTPS_FIPS forces the system to use the FIPS libraries. If HTTPS_FIPS is specified, but no FIPS libraries are present, non-FIPS libraries are used instead.

    When specified in this way, the user name and password are passed unencrypted, as part of the URL.

  • HEADER clause   When creating HTTP web service client procedures, use this clause to modify HTTP request header entries, add new ones, or suppress existing headers. The specification of headers closely resembles the format specified in RFC2616 Hypertext Transfer Protocol — HTTP/1.1, and RFC822 Standard for ARPA Internet Text Messages, including the fact that only printable ASCII characters can be specified for HTTP headers, and they are case-insensitive. Following are a few key points regarding HTTP header specification:

    • Header/value pairs can be delimited by \n or \x0d\n, specifying Line Feed (<LF>), or Carriage Return and Line Feed(<CR><LF>), respectively.
    • A header is delimited from its value using a colon (:), and therefore cannot contain a colon.
    • A header followed by :\n, or an end of line, specifies a header with no value. Similarly, a header with no colon or value after. For example, HEADER 'Date', specifies that the Date header not be included. Suppressing headers, or their values, can cause unexpected results. See Modifying HTTP headers.
    • Folding of long header values is supported, provided one or more white spaces immediately follow the \n. For example, the following HEADER specification, and resulting HTTP output, are semantically equivalent:
      ... HEADER 'heading1: This long value\n is a really long value for heading1\n
      heading1:This long value is a really long value for heading1<CR><LF>
    • Multiple contiguous white spaces, including folding, results in a single white space.
    • Parameter substitution is supported for this clause.

    This example shows how to add static user-defined headers:

    CREATE PROCEDURE http_client() URL 'http://localhost/getdata' 
      TYPE 'http:get' HEADER 'UserHeader1:value1\nUserHeader2:value2';

    This example shows how to add new parameter-substituted user-defined headers:

    CREATE PROCEDURE http_client( headers LONG VARCHAR ) URL 'http://localhost/getdata' 
    TYPE 'http:get' HEADER '!headers';
    CALL http_client( 'NewHeader1:value1\nNewHeader2:value2' );

    For more information about using HTTP headers, see Working with HTTP headers.

  • SOAPHEADER clause   When declaring a SOAP web service as a procedure, use this clause to specify one or more SOAP request header entries. A SOAP header can be declared as a static constant, or can be dynamically set using the parameter substitution mechanism (declaring IN, OUT, or INOUT parameters for hd1, hd2, and so on). A web service procedure can define one or more IN mode substitution parameters, and a single INOUT or OUT substitution parameter.

    The following example illustrates how a client can specify the sending of several header entries with parameters and receiving the response SOAP header data:

      URL 'localhost/some_endpoint' 
      SOAPHEADER '!hd1!hd2!hd3';

    For more information about using SOAP headers, see Working with SOAP headers.

  • TYPE clause   Used to specify the format used when making the web service request. If SOAP is specified or no type clause is included, the default type SOAP:RPC is used. HTTP implies HTTP:POST. Since SOAP requests are always sent as XML documents, HTTP:POST is always used to send SOAP requests. See also: Creating web service client functions and procedures.

  • NAMESPACE clause   Applies to SOAP client procedures only. This clause identifies the method namespace usually required for both SOAP:RPC and SOAP:DOC requests. The SOAP server handling the request uses this namespace to interpret the names of the entities in the SOAP request message body. The namespace can be obtained from the WSDL (Web Services Description Language) of the SOAP service available from the web service server. The default value is the procedure's URL, up to but not including the optional path component.

  • CERTIFICATE clause   To make a secure (HTTPS) request, a client must have access to the certificate used by the HTTPS server. The necessary information is specified in a string of semicolon-separated key/value pairs. The certificate can be placed in a file and the name of the file provided using the file key, or the whole certificate can be placed in a string, but not both. The following keys are available:

    Key Abbreviation Description
    file The file name of the certificate.
    certificate cert The certificate itself.
    company co The company specified in the certificate.
    unit The company unit specified in the certificate.
    name The common name specified in the certificate.

    Certificates are required only for requests that are either directed to an HTTPS server, or can be redirected from a non-secure to a secure server.

  • CLIENTPORT clause   Identifies the port number on which the HTTP client procedure communicates using TCP/IP. It is provided for and recommended only for connections across firewalls, as firewalls filter according to the TCP/UDP port. You can specify a single port number, ranges of port numbers, or a combination of both; for example, CLIENTPORT '85,90-97'.

    See ClientPort protocol option [CPORT].

  • PROXY clause   Specifies the URI of a proxy server. For use when the client must access the network through a proxy. Indicates that the procedure is to connect to the proxy server and send the request to the web service through it.

  • SET clause   Specifies protocol-specific behavior options for HTTP and SOAP. The following list describes the supported SET options. CHUNK and VERSION apply to the HTTP protocol, and OPERATION applies to the SOAP protocol. Parameter substitution is supported for this clause.

    • CH or CHUNK   This option allows you to specify whether to use chunking. Chunking allows HTTP messages to be broken up into several parts. Possible values are ON (always chunk), OFF (never chunk), and AUTO (chunk only if the contents, excluding auto-generated markup, exceeds 2048 bytes). For example, the following SET clause enables chunking:
      ... SET 'HTTP ( CHUNK=ON )' ...

      If the CHUNK option is not specified, the default behavior is AUTO. If chunking fails in AUTO mode with a status of 505 ('HTTP Version Not Supported'), or with 501 ('Not Implemented'), client retries the request without chunked encoding.

      Since CHUNK mode is a transfer encoding supported starting in HTTP version 1.1, setting CHUNK to ON requires that the version (VER) be set to 1.1, or not be set at all, in which case 1.1 is used as the default version.

    • VER or VERSION   This option allows you to specify the version of HTTP protocol that is used for the format of the HTTP message. For example, the following SET clause sets the HTTP version to 1.1:
      ... SET 'HTTP ( VERSION=1.1 )' ...

      Possible values are 1.0 and 1.1. If VERSION is not specified:

      • if CHUNK is set to ON, 1.1 is used as the HTTP version
      • if CHUNK is set to OFF, 1.0 is used as the HTTP version
      • if CHUNK is set to AUTO, either 1.0 or 1.1 is used, depending on whether the client is sending in CHUNK mode

    • OP or OPERATION   This option allows you to specify the name of the SOAP operation, if it is different from the name of the procedure you are creating. The value of OPERATION is analogous to the name of a remote procedure call. For example, if you wanted to create a procedure called accounts_login that calls a SOAP operation called login, you would specify something like the following:
      CREATE PROCEDURE accounts_login( 
            name LONG VARCHAR, 
            pwd LONG VARCHAR )
         SET 'SOAP ( OPERATION=login )' 

      If the OPERATION option is not specified, the name of the SOAP operation must match the name of the procedure you are creating.

    The following statement shows how several protocol-option settings are combined in the same SET clause:

    CREATE PROCEDURE accounts_login( 
          name LONG VARCHAR, 
          pwd LONG VARCHAR )
       SET 'HTTP ( CHUNK=ON; VERSION=1.1 ), SOAP( OPERATION=login )' 

    For more information about creating web services, including examples, see SQL Anywhere web services.


The CREATE PROCEDURE statement creates a procedure in the database. Users with DBA authority can create procedures for other users by specifying an owner. A procedure is invoked with a CALL statement.

If a stored procedure returns a result set, it cannot also set output parameters or return a return value.

For web service client procedures, parameter values are passed as part of the request. The syntax used depends on the type of request. For HTTP:GET, the parameters are passed as part of the URL; for HTTP:POST requests, the values are placed in the body of the request. Parameters to SOAP requests are always bundled in the request body.

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. See Referencing temporary tables within procedures.


Must have RESOURCE authority, unless creating a temporary procedure.

Must have DBA authority for external procedures or to create a procedure for another user.

Side effects

Automatic commit.

See also
Standards and compatibility
  • SQL/2003   Persistent Stored Module feature. The syntax extensions for Java result sets are as specified in the optional J621 feature.


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))
   DECLARE prod_name CHAR(20);
   SELECT name INTO prod_name FROM 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'
      SET type = 'UNKNOWN'

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)
   DECLARE err_notfound EXCEPTION
   FOR SQLSTATE '02000';
      SELECT CompanyName,
          CAST(SUM(SalesOrderItems.Quantity *
          Products.UnitPrice) AS INTEGER) VALUE
      FROM 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;
      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;