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