I am working on a testing notebook where the table that is tested can be given as a widget. I wanted to write it in SQL. The notebook does the following steps in a cycle that should run 10 times:
1. Store the starting version of a delta table in a variable
2. Do some data transformation
3. Restore the table to its original version
It seems to me that the above process cannot be written in SQL currently. If I try to give a value to a variable by a dynamic SQL within the FOR loop, the variable returns 0.
Example below (I did not include the widgets in the example, but widgets are the reason the SET sql statement is run by EXECUTE IMMEDIATE, and not directly). The result should be 5 but instead it shows null.
It is also remarkable that within a BEGIN END statement, SET VARIABLE should not be accepted, it should only work with SET sql =. However, if I omit the VARIABLE word from the definition of sql, then it throws an error.
BEGIN
DECLARE sum INT DEFAULT 0;
DECLARE start_version INT;
DECLARE sql STRING;
testCycle: FOR SELECT * FROM range(1, 2) DO
SET sql = 'SET VARIABLE start_version = 5';
EXECUTE IMMEDIATE sql;
SELECT start_version;
END FOR testCycle;
END;
