Advantage SQL Engine
The EXECUTE IMMEDIATE statement is used to execute a dynamically constructed SQL statement or SQL script.
EXECUTE IMMEDIATE char_expr;
The EXECUTE IMMEDIATE statement prepares the SQL statement or SQL script in the char_expr and executes it immediately. The prepared statement or script is freed immediately after execution. The SQL statement or script is executed within its own scope and it has no access to variables declared in the outer script. However, runtime and custom exceptions raised while executing the SQL statements contained in the char_expr may be caught and handled by the outer script.
// A sample stored procedure that adds a auto increment column to a
// table if one does not exist.
// Note that the table name and the column name in the dynamically constructed
// SQL statement are not delimited so this stored procedure only works if
// the tblName and colName do not contain space or other special characters.
CREATE PROCEDURE AddAutoInc( tblName cichar(20), colName cichar(20))
DECLARE col cursor, __input cursor as select * FROM __input;
DECLARE stmt string;
// Check to see if the column already exists
OPEN col as SELECT * FROM system.columns
WHERE parent = __input.tblName AND
name = __input.colName;
IF FETCH col THEN
// already exists
// column does not exists, add it
// Construct the ALTER TABLE statement
stmt = 'ALTER TABLE ' + __input.tblName +
' ADD ' + __input.colName + ' autoinc ';
EXECUTE IMMEDIATE stmt;