SQL recursive call

ID	Name	Parent_id	Audit_start	Audit_end
1	Client1	<Null>	2008.08.02	<Null>
2	Division1	1	2008.08.02	<Null>
3	Division2	2	2008.08.02	<Null>

----------------------------------------------------------------------
Declare @ clientid int
Set @ clientid = 1

-----------------------------------------------------------------
-- Get all subclients belong to this client including itself.
Declare @ tbl_clients table
(
Client_id int,
Client_name varchar (100 ),
Level int
)
----------------------------------------------------------
-- Insert itself
Insert into @ tbl_clients (client_id, client_name, level)
Select @ clientid, name, 0
From clients
Where id = @ clientid and audit_end is null

-- Insert its children
Declare @ level int
Set @ level = 1

Insert into @ tbl_clients
Select ID, name, @ level from clients where parent_id = @ clientid and audit_end is null

While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert into @ tbl_clients
Select clients. ID, clients. Name, @ level
From clients, @ tbl_clients TMP
Where clients. parent_id = TMP. client_id
And TMP. Level = @ level-1
And clients. audit_end is null
End

Select * From @ tbl_clients

Select * from clients where audit_end is null