Method 1: Using execute immediate

Use execute immediate to send a complete Transact-SQL statement, stored in a host variable or literal string, to Adaptive Server Enterprise. The statement cannot return any results—you cannot use this method to execute a select statement.

The dynamically entered statement executes as many times as the user invokes it during a session. With this method:

  1. The Embedded SQL program passes the text to Adaptive Server Enterprise.

  2. Adaptive Server Enterprise verifies that the statement can execute dynamically and does not return rows.

  3. Adaptive Server Enterprise compiles and executes the statement.

With execute immediate, you can let the user enter all or part of a Transact-SQL statement.

The syntax for execute immediate is:

exec sql [at connection_name] execute immediate
     {:host_variable | "string"} end-exec 


Embedded SQL sends the statement in host_variable or string to Adaptive Server Enterprise without any processing or checking. If the statement attempts to return results or fails, an error occurs. You can test the value of SQLCODE after executing the statement or use the whenever statement to set up an error handler. See Chapter 8, “Handling Errors” for information about handling errors in Embedded SQL programs.