|
According to EXECUTE IMMEDIATE statement docs:
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. :)
|
|
See this older comment from Mark:
In my understanding, your case a) uses one single SET statement whereas case b) uses a batch (i.e. several statements). Understood. Thanks.
(09 Jul '19, 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 :)
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 '19, 06:03)
Volker Barth
|
