Using Execute Immediate in a script being read into ISQL

[former_SQLA_member1694871]

I'm attempting to create a set of Sequences that correlate to table names in a set group of tables. From what I've been reading, it seems like "Execute Immediate" is for use within stored procedures.

The help file says "EXECUTE IMMEDIATE statement [SP]" Meaning that it's atomic. Needs to be used in a stored procedure, or trigger.

Is there any reason an EXECUTE IMMEDIATE would not work within a script that's being read straight into ISQL?

Thanks!

Jeff Gibson
Intercept Solutions
Nashville, TN

[Breck_Carter]

EXECUTE IMMEDIATE is a server-side statement, not an ISQL statement like READ or OUTPUT, so it has to be sent to the server in order to execute.

HOWEVER, you do NOT need to store the EXECUTE IMMEDIATE inside a procedure or trigger in the server, you can run it directly via ISQL.

If you see this [sassen-fracken-fricken-fracken] message

Procedure 'IMMEDIATE' not found

when you execute a statement like this in ISQL

EXECUTE IMMEDIATE STRING ( 'CREATE TABLE Hello_', DATEFORMAT ( CURRENT DATE, 'Ddddddddd' ), ' ( c INTEGER )' );

it is probably because SQL Anywhere thinks you are using [spit] Transact SQL.

There are two workarounds:

(1) Use the [spit] Transact SQL "Syntax 2" EXECUTE ( expression ) statement

EXECUTE ( STRING ( 'CREATE TABLE Hello_', DATEFORMAT ( CURRENT DATE, 'Ddddddddd' ), ' ( c INTEGER )' ) );

SELECT table_name FROM SYSTABLE WHERE table_name LIKE 'Hello%';

table_name
'Hello_Monday'

(2) Surround your EXECUTE IMMEDIATE with a BEGIN END block

BEGIN
EXECUTE IMMEDIATE STRING ( 'CREATE TABLE Hello_', DATEFORMAT ( CURRENT DATE + 1, 'Ddddddddd' ), ' ( c INTEGER )' );
END;

SELECT table_name FROM SYSTABLE WHERE table_name LIKE 'Hello%';

table_name
'Hello_Monday'
'Hello_Tuesday'

[VolkerBarth]

What version are you using? If v17, you could check whether the new features of "indirect identifiers " or "TABLE REF data types" help to parametrize your statements without requiring EXECUTE IMMEDIATE.

Note, with all versions, you can also directly parametrize DBISQL scripts by using the PARAMETERS statement and then supply the parameters on the DBISQL command line.

---

All that being said, you certainly can use dynamic SQL via EXECUTE IMMEDIATE outside of procedures or statement blocks or SQL batches, say simply running something like in a DBISQL session:

execute immediate 'message ''Test'' to console;'

However, that's not that useful IMHO: You will usually only need dynamic SQL when there is something to parametrize, so you usually will need variables (or query results) to supply those dynamic values, and declaring and using variables is (with some exceptions) only possible within statement blocks. Note that a statement block itself can be used outside of procedures.

[SP] The statement is for use in stored procedures, triggers, or batches.

So in my understanding, that document convention does mean "...is meant to be used in ...", not necessarily "...is only usable/allowed within ...".