Create Java List From Recursive SQL Query

Question

I have a record that I need to do some validation on. This record can potentially have multiple children. I need to create a collection (I was planning on using a standard list) containing the ID of every child record but this needs to be recursive (i.e. each child record can also have child records of its own, and I need each of those IDs as well, ad infinitem).I'm using MSSQL Server for my DB but I'm not too sure how I would structure the necessary SQL query.

This is what I have tried;

DECLARE @Id VARCHAR(30) = '[Record Id]';
WITH cte AS 
 (
  SELECT record.id, record.parentid
  FROM records r
  WHERE id = @Id
  UNION ALL
  SELECT record.id, wo.parentid
  FROM records r JOIN cte c ON wo.parentid = c.id
  )
  SELECT id
  FROM cte;

I get the following error:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

question from:https://stackoverflow.com/questions/65900546/create-java-list-from-recursive-sql-query

Answer 1

Waitting for answers