According to EXECUTE IMMEDIATE statement docs:

Only global variables can be referenced in a statement executed by EXECUTE IMMEDIATE.

I have three cases that show this is not always true at least in builds 16.0.0.2614 and 17.0.10.5771 (and 11.0.1.3158).

Case a) works despite referencing local variable:

BEGIN
    DECLARE @a BIT;
    SET @a = 0;
    EXECUTE IMMEDIATE 'SET @a = IF @a = 0 THEN 1 ELSE @a ENDIF';
    SELECT @a;
END
Case b) should do the same but throws Column '@b' not found error (however, this behavior meets the docs):
BEGIN
    DECLARE @b BIT;
    SET @b = 0;
    EXECUTE IMMEDIATE 'IF @b = 0 THEN SET @b = 1 END IF';
    SELECT @b;
END
Case c) works (this behavior meets the docs if we name connection variables as global):
BEGIN
    CREATE VARIABLE @c BIT;
    SET @c = 0;
    EXECUTE IMMEDIATE 'IF @c = 0 THEN SET @c = 1 END IF';
    SELECT @c;
END
Could someone from SAP explain the reasons of the different behavior in cases a) and b)? Can it be fixed/improved in future releases? I would expect that case b) would work too (and the docs would be modified accordingly), i. e. NO, I'm NOT expecting case a) to stop working just to meet the docs. :)

asked 09 Jul, 01:01

Arthoor's gravatar image

Arthoor
1.3k334965
accept rate: 11%

edited 09 Jul, 01:31


See this older comment from Mark:

As long as the EXECUTE IMMEDIATE statement is a single statement then the statement runs in the same context as the calling block - if it is a compound statement, then it runs as a batch an hence runs within its own scope and would not have access to the calling scope's variables.

In my understanding, your case a) uses one single SET statement whereas case b) uses a batch (i.e. several statements).

permanent link

answered 09 Jul, 05:43

Volker%20Barth's gravatar image

Volker Barth
33.9k333487718
accept rate: 33%

Understood. Thanks.

(09 Jul, 08:28) Arthoor

Volker's answer is correct... but it omits Mark's first sentence where he says the "statement about needing to use CREATE VARIABLE is not correct"...

...hence this rant... :)

In the real world the vast majority of EXECUTE IMMEDIATE statements are single statements rather than compound statements. In particular, a huge SELECT, INSERT, DELETE or UPDATE statement is still a single statement no matter how many derived tables, CASE and IF expressions, and JOIN conditions involving procedure calls it contains.

As such, an EXECUTE IMMEDIATE can contain as many references to local variables you want, and you do NOT have to use CREATE VARIABLE.

In particular, Foxhound contains dozens (hundreds?) of EXECUTE IMMEDIATEs and not one of them had to resort to CREATE VARIABLE.

...in fact, I was not even aware of the compound statement restriction :)

permanent link

answered 26 Jul, 09:56

Breck%20Carter's gravatar image

Breck Carter
29.3k486649957
accept rate: 20%

edited 26 Jul, 10:17

Yes, and even statements to fill local variables based on a dynamic query can usually be written as one single statement, such as

Set myVar = (query)

or via SELECT INTO. I guess Arthoor is fully aware of that. Personally, I ran into the compound statement limit several times, otherwise Mark would not have had to comment then...

(27 Jul, 06:03) Volker Barth
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:

×1

question asked: 09 Jul, 01:01

question was seen: 168 times

last updated: 27 Jul, 06:04

Related questions