EXECUTE IMMEDIATE with variable references

[former_SQLA_member1694875]

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

[VolkerBarth · Accepted solution]

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

[former_SQLA_member1694875 · Accepted solution]

Understood. Thanks.

[Breck_Carter]

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 🙂

[VolkerBarth]

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