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.