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

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

EXECUTE statement [ESQL]

Executes a prepared SQL statement.

  • General use
    EXECUTE statement
    [ USING { hostvar-list | [ SQL ] DESCRIPTOR sqlda-name } ]
    [ INTO { into-hostvar-list | [ SQL ] DESCRIPTOR into-sqlda-name } ]
    [ ARRAY :row-count ]
    row-count : integer | hostvar
    statement :   identifier | hostvar | string 
    sqlda-name : identifier
    into-sqlda-name :  identifier
  • Execute immediately
    statement :  string | hostvar
  • USING clause

    Results from a SELECT statement or a CALL statement are put into either the variables in the variable list or the program data areas described by the named SQLDA. The correspondence is one-to-one from the OUTPUT (selection list or parameters) to either the host variable list or the SQLDA descriptor array.

  • INTO clause

    If EXECUTE INTO is used with an INSERT statement, the inserted row is returned in the second descriptor. For example, when using auto-increment primary keys or BEFORE INSERT triggers that generate primary key values, the EXECUTE statement provides a mechanism to re-fetch the row immediately and determine the primary key value that was assigned to the row. The same thing can be achieved by using @@identity with auto-increment keys.

  • ARRAY clause

    The optional ARRAY clause can be used with prepared INSERT statements to allow wide inserts, which insert more than one row at a time and which can improve performance. The integer value is the number of rows to be inserted. The SQLDA must contain a variable for each entry (number of rows * number of columns). The first row is placed in SQLDA variables 0 to (columns per row)-1, and so on. Similarly, the ARRAY clause can be used for wide updates, deletes, and merges using prepared UPDATE, DELETE, and MERGE statements.


The EXECUTE statement can be used for any SQL statement that can be prepared. Cursors are used for SELECT statements or CALL statements that return many rows from the database.

After successful execution of an INSERT, UPDATE, DELETE, or MERGE statement, the sqlerrd[2] field of the SQLCA (SQLCOUNT) is filled in with the number of rows affected by the operation.

  • Execute

    Execute the named dynamic statement, which was previously prepared. If the dynamic statement contains host variable placeholders that supply information for the request (bind variables), either the sqlda-name must specify a C variable which is a pointer to a SQLDA containing enough descriptors for all the bind variables occurring in the statement, or the bind variables must be supplied in the hostvar-list.

  • Execute immediately

    A short form to PREPARE and EXECUTE a statement that does not contain bind variables or output. The SQL statement contained in the string or host variable is immediately executed, and is dropped on completion.

When performing wide insert, update and delete operations (ARRAY clause), host variables must be simple names and the rows of the wide update must be the same data type.


The required privileges depend on the statement being executed.

Side effects


  • ANSI/ISO SQL Standard

    The EXECUTE statement comprises part of optional ANSI/ISO SQL Language Feature B031, "Basic dynamic SQL". The INTO clause is part of optional ANSI/ISO SQL Language Feature B032, "Extended dynamic SQL". The ARRAY clause is not in the standard.

    The EXECUTE IMMEDIATE statement supported with Embedded SQL is also part of optional ANSI/ISO SQL Language Feature B031.


This example executes a DELETE statement.

'DELETE FROM Employees WHERE EmployeeID = 105';

This example executes a prepared DELETE statement.

'DELETE FROM Employees WHERE EmployeeID = :a';
EXEC SQL EXECUTE del_stmt USING :employee_number;

This example executes a prepared query.

'SELECT Surname FROM Employees WHERE EmployeeID = :a';
EXEC SQL EXECUTE sel1 USING :employee_number INTO :surname;