SQL While do loops

[elgeo]

Hello. Could you please suggest a workaround for a while do loop in Databricks SQL?

WHILE LSTART>0 DO SET LSTRING=CONCAT(LSTRING, VSTRING2)

Thank you in advance

[ashishasr]

hi, In Databricks SQL stored procedures (Unity Catalog, DBR ≥ 17.0), the WHILE ... DO loop is supported, 
equivalent code for this, 

WHILE LSTART > 0 DO
    SET LSTRING = CONCAT(LSTRING, VSTRING2);
END WHILE;

[szymon_dybczak]

Hi, yes it's possbile. Refer to below thread:

SQL Stored Procedure in Databricks - Databricks Community - 133371

[nayan_wylde]

@elgeo Here are two alternatives.

1. Use a recursive CTE

WITH RECURSIVE loop_cte (lstart, lstring) AS (
  SELECT 5 AS lstart, '' AS lstring
  UNION ALL
  SELECT lstart - 1, CONCAT(lstring, 'VSTRING2')
  FROM loop_cte
  WHERE lstart > 1
)
SELECT * FROM loop_cte;

2. you can use pyspark. If you are running the code in notebook.

lstart = 5
lstring = ""
vstring2 = "VSTRING2"

while lstart > 0:
    lstring += vstring2
    lstart -= 1