Using Execute Immediate in a script being read into ISQL

[#1]

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'