Recursive Org List

CREATE FUNCTION [f_GetParentOrgs] ( @org_id int ) RETURNS @parentOrgs TABLE ( ID int IDENTITY PRIMARY KEY, org_id int ) AS BEGIN DECLARE @row int DECLARE @org int INSERT INTO @parentOrgs ( org_id ) SELECT organizationParentID FROM t_organization WITH (NOLOCK) WHERE organizationID = @org_id AND organizationParentID NOT IN ( SELECT org_id FROM @parentOrgs ) SELECT @row = count(1) FROM @parentOrgs WHILE @row > 0 BEGIN SELECT @org = org_id FROM @parentOrgs WHERE ID = @row INSERT INTO @parentOrgs ( org_id ) SELECT org_id FROM dbo.f_GetParentOrgs( @org ) WHERE org_id NOT IN ( SELECT org_id FROM @parentOrgs ) SET @row = @row - 1 END --this is to include original org parameter in list if needed INSERT INTO @parentOrgs ( org_id ) VALUES ( @org_id ) RETURN END