SQL Server 自訂函數 返回樹結構函數

資料庫結構:

表內的資料:

自訂函數: 遞迴查出 樹下所有節點 ,參數是 父id

 create  function sss(@id as int)  returns @t table  (    id int not null,    name int not null,    pid int null  )  as  begin  declare @lay as int;  insert into @t   select * from tree where pid =@id;    select @lay = min(id) from tree where pid =@id; --第一次 @lay=5    while @lay is not null  begin      insert into @t     select * from sss(@lay);        select @lay=min(id) from tree    where id>@lay and pid=@id  end  return;  end  go     

.net代碼:

 string cons = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();        using (SqlConnection con=new SqlConnection(cons))        {            if (con.State==ConnectionState.Closed)            {                con.Open();                            }            string sql = "select * from sss(@id)";            SqlCommand cmd = new SqlCommand(sql,con);                      cmd.CommandType = CommandType.Text;                       cmd.Parameters.Add(new SqlParameter("@id", DbType.Int32)).Value = 4;            cmd.Parameters.Add("@re",DbType.String);            cmd.Parameters["@re"].Direction = ParameterDirection.ReturnValue;            SqlDataReader dr = cmd.ExecuteReader();            while (dr.Read())            {                int i = 0;                Response.Write(dr[0].ToString() + "\t\t\t" +dr[1].ToString() +"\t\t\t"+  dr[2].ToString() + "</br>");                i++;                            }            con.Close();

 }

實現的效果如下: