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

asked 01 Jul, 16:43

Jeff%20Gibson's gravatar image

Jeff Gibson
1.4k314759
accept rate: 21%


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

permanent link

answered 02 Jul, 04:08

Volker%20Barth's gravatar image

Volker Barth
32.1k327470688
accept rate: 32%

edited 02 Jul, 04:12

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'
permanent link

answered 02 Jul, 05:07

Breck%20Carter's gravatar image

Breck Carter
27.6k473629907
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×86
×31
×6
×5

question asked: 01 Jul, 16:43

question was seen: 43 times

last updated: 02 Jul, 05:07