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.3k334964
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.8k330486716
accept rate: 33%

Understood. Thanks.

(09 Jul, 08:28) Arthoor
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: 83 times

last updated: 09 Jul, 08:28

Related questions