How can i write a recursive function for this?
select col1, col2, col3
From BaseTable
Left Join dbo.Table6 Table6 on (BaseTable.z = Table6.ID6)
Left Join dbo.Table5 Table5 on (BaseTable.z = Table5.ID5)
Left Join dbo.Table4 Table4 on (Table5.ID4 = Table4.ID4)
Left Join dbo.Table3 Table3 on (Table4.ID3 = Table3.ID3)
Left Join dbo.Table2 Table2 on (Table3.ID2 = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where ID6 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') + '/' + ISNULL(Table3.MyCol,'')
+ '/' + ISNULL(Table4.MyCol,'')+ '/' + ISNULL(Table5.MyCol,'') IS NOT NULL + '/' + ISNULL(Table6.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.Table5 Table5 on (BaseTable.z = Table5.ID5)
Left Join dbo.Table4 Table4 on (Table5.ID4 = Table4.ID4)
Left Join dbo.Table3 Table3 on (Table4.ID3 = Table3.ID3)
Left Join dbo.Table2 Table2 on (Table3.ID2 = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where ID5 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') + '/' + ISNULL(Table3.MyCol,'')
+ '/' + ISNULL(Table4.MyCol,'')+ '/' + ISNULL(Table5.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.Table4 Table4 on (BaseTable.z = Table4.ID4)
Left Join dbo.Table3 Table3 on (Table4.ID3 = Table3.ID3)
Left Join dbo.Table2 Table2 on (Table3.ID2 = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where Table4.ID4 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') + '/' + ISNULL(Table3.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.Table3 Table3 on (BaseTable.z = Table3.ID3)
Left Join dbo.Table2 Table2 on (Table3.ID2 = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where Table3.ID3 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') + '/' + ISNULL(Table3.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.Table2 Table2 on (BaseTable.z = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where Table2.ID2 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.TableZ TableZ on (BaseTable.z = TableZ.ID1)
where TableZ.ID1 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' IS NOT NULL