SQL Server user-defined functions return tree Functions

Database Structure:

Data in the table:

Custom function: recursively identifies all nodes under the tree. The parameter is the parent 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; -- First @ 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 code:

 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();

}

The implementation result is as follows: