I have created a parameterized U-SQL procedure and I want to call it recursively by employee id. Is there a way we can implement recursion in U-SQL? Thanks!
2 Answers
U-SQL does indeed support recursion and you can view the Recursive TVF example here. However, recursive approaches aren't something that generally scale very well, so you may need to consider a different approach.
Paul Andrew describes another approach to recursion using Powershell here.
If you could provide some sample data and expected results, I'm sure someone will be able to help you. I would guess you don't need a recursive approach, you simply need a set-based one.
Comments
To add to Bob's answer (not sure if I should edit his answer or provide my own, so here I go):
- Recursive TVFs have a nesting level limit of 50.
- You cannot/should not call Procedures recursively, since they produce side-effects (like writing into tables or files). Instead, write a recursive TVF that produces the rowset that you then want to write in a procedure.
But as Bob mentions, please see if you can find a set-based solution or using an existing feature that does not involve recursion.
