sql - How to handle recursion in a flat table? -

i have 2 tables keep track of permissions groups of users. first table 2 columns, identifier , name, used solely names of permissions. second table permissions applied , parent permissions assigned create hierarchy. problem i'm using joins create permission hierarchy "string" based on parent permissions and, without knowing how deep parent recursion might go, have no way of knowing how many joins make. questions is, there more correct way solve problem?

i've included complete working script, stripped unnecessary columns:

create table #temppermissions (     permission_id int identity,     permission varchar(50) )  create table #tempapppermissions (     apppermission_id int identity,     permission_id int,     parent_id int )  insert #temppermissions values ('users') insert #temppermissions values ('add') insert #temppermissions values ('edit') insert #temppermissions values ('remove') insert #temppermissions values ('permissions') insert #temppermissions values ('configure')  insert #tempapppermissions values (1, -1) insert #tempapppermissions values (2, 1) insert #tempapppermissions values (3, 1) insert #tempapppermissions values (4, 1) insert #tempapppermissions values (5, 1) insert #tempapppermissions values (6, 5)  select app.apppermission_id,        (case when not child3.permission null '/' + child3.permission else '' end)+        (case when not child2.permission null '/' + child2.permission else '' end)+        '/' + child1.permission permissionstring #tempapppermissions app inner join #temppermissions child1    on child1.permission_id = app.permission_id left join #tempapppermissions parent1   on parent1.apppermission_id = app.parent_id left join #temppermissions child2   on child2.permission_id = parent1.permission_id left join #tempapppermissions parent2   on parent2.apppermission_id = parent1.parent_id left join #temppermissions child3   on child3.permission_id = parent2.permission_id  drop table #temppermissions, #tempapppermissions 

this provides me results:

apppermission_id    permissionstring 1   /users 2   /users/add 3   /users/edit 4   /users/remove 5   /users/permissions 6   /users/permissions/configure 

this works fine is, if go parent deep with:

insert #temppermissions values ('reports') insert #tempapppermissions values (7, 6) 

i have compensate set of joins , case expression in select statement:

(case when not child4.permission null '/' + child4.permission else '' end)+  ...  left join #tempapppermissions parent3   on parent3.apppermission_id = parent2.parent_id left join #temppermissions child4   on child4.permission_id = parent3.permission_id 

if not, end losing topmost parent on last result:

1   /users 2   /users/add 3   /users/edit 4   /users/remove 5   /users/permissions 6   /users/permissions/configure 7   /permissions/configure/reports 

technically, repeat number of times compensate how deep structure may go, have feeling there better approach problem. in advance.

i use cte (common table expressions).

;with t ( select 1 iteration, p.permission_id permissionid, p.permission_id, cast(n'/' + p.permission nvarchar(max)) permission #temppermissions p union select iteration + 1, t.permissionid, p.parent_id, coalesce(n'/' + (select s.permission #temppermissions s s.permission_id = p.parent_id), n'') +  t.permission t inner join #tempapppermissions p on t.permission_id = p.permission_id ) select permissionid, permission t permission_id = -1 order permissionid, iteration 

let me know if helps!