Recursive processing of tree structures (Table structures)

/* Recursively process the tree structure (table structure) to recursively find the city, from small to large, or from large to small. * // * And so on. Similarly, the directory tree structure. I want to write a function to input the Department ID and get the corresponding department structure immediately. For example, enter 8. The result is the marketing department-Southeast market-Shanghai input 9. the Marketing Department-Northwest market-Beijing Input 6 is the marketing department-Northwest market input 3 is the marketing department input 1 is all departments */-- Create Table tablea (deptid int, deptname nvarchar (100), parentid INT) insert into tableaselect 1, 'all departments ', 0 Union allselect 2, 'Finance Department', 1 Union allselect 3, 'marketing Department ', 1 Union allselect 4, 'warehouse management', 1 Union allselect 5, 'northeast market ', 3 Union allselect 6, 'northwest market', 3 Union allselect 7, 'southeast market ', 3 Union allselect 8, 'shanghai', 7 Union allselect 9, 'beijing', 6 -- create the function if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [alldept] ') and xtype in (n'fn', n'if', n'tf') Drop function [DBO]. [alldept] Go create function alldept (@ ideptid INT) returns nvarchar (1000) asbegin declare @ vreturnvalue nvarchar (1000), @ iparentid int, @ vcurrentdeptname nvarchar (200) select @ vreturnvalue = '', @ vcurrentdeptname ='' if (exists (select top 1 0 from tablea where deptid = @ ideptid and parentid = 0 )) begin select @ vreturnvalue = @ vreturnvalue + deptname + '-' from tablea where parentid <> 0 return (@ vreturnvalue) end if (exists (select top 1 0 from tablea where deptid = @ ideptid and parentid = 1 )) begin select @ vreturnvalue = @ vreturnvalue + deptname from tablea where deptid = @ ideptid and parentid = 1 -- Return (@ vreturnvalue) -- set @ vreturnvalue = @ vreturnvalue + DBO. alldept (@ iparentid) end else begin select @ iparentid = parentid, @ vcurrentdeptname = deptname from tablea where deptid = @ ideptid set @ vreturnvalue = @ vreturnvalue + @ vcurrentdeptname + '-' + DBO. alldept (@ iparentid) -- Return (@ vreturnvalue) end return (@ vreturnvalue) end goset quoted_identifier off goset ansi_nulls on go select *, DBO. alldept (deptid) alldeptname from tablea select * From tablea -- display result deptid deptname alldeptname1 all departments 0 Finance Department-Marketing Department-Warehouse Management-Northeast market-Northwest market-Southeast market-Shanghai-Beijing-2 Finance Department 1 Finance Department 3 Marketing Department 1 Marketing Department 4 Warehouse Management 1 Warehouse Management 5 Northeast market 3 Northeast market-Marketing Department 6 northwest market 3 northwest market-Marketing Department 7 southeast market 3 southeast market-Marketing Department 8 Shanghai 7 Shanghai-Southeast Market market-Marketing Department 9 Beijing 6 Beijing-Northwest market-marketing department -- delete test environment drop table tableaddrop table DBO. alldept