The EXECUTE IMMEDIATE statement allows statements to be constructed inside procedures using a combination of literal strings (in quotes) and variables. For example, the following procedure includes an EXECUTE IMMEDIATE statement that creates a table.
CREATE PROCEDURE CreateTableProcedure( IN tablename char(128) ) BEGIN EXECUTE IMMEDIATE 'CREATE TABLE ' || tablename || '(column1 INT PRIMARY KEY)' END; |
The EXECUTE IMMEDIATE statement can be used with queries that return result sets. For example:
CREATE PROCEDURE DynamicResult( IN Columns LONG VARCHAR, IN TableName CHAR(128), IN Restriction LONG VARCHAR DEFAULT NULL ) BEGIN DECLARE Command LONG VARCHAR; SET Command = 'SELECT ' || Columns || ' FROM ' || TableName; IF ISNULL( Restriction,'') <> '' THEN SET Command = Command || ' WHERE ' || Restriction; END IF; EXECUTE IMMEDIATE WITH RESULT SET ON Command; END; |
The following statement calls this procedure:
CALL DynamicResult( 'table_id,table_name', 'SYSTAB', 'table_id <= 10'); |
table_id | table_name |
---|---|
1 | ISYSTAB |
2 | ISYSTABCOL |
3 | ISYSIDX |
... | ... |
In ATOMIC compound statements, you cannot use an EXECUTE IMMEDIATE statement that causes a COMMIT, as COMMITs are not allowed in that context.
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |