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. 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.

  2. Adaptive Server verifies that the statement is not a select statement.

  3. Adaptive Server 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};

where:

Embedded SQL sends the statement in host_variable or string to Adaptive Server 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 for information about handling errors in Embedded SQL programs.