SQL Server Function: GetdeptTree - Hierarchical Department Structure

This is a SQL Server function that returns a table variable called '@t'. The function is called 'GetdeptTree' and it takes no arguments.

The table variable '@t' has the following columns:

• 'id': integer value representing the department id
• 'departmentname': string value representing the department name
• 'supdepid': integer value representing the id of the parent department
• 'subcompanyid1': integer value representing the id of the subcompany
• 'code': string value representing the department code
• 'showorder': integer value representing the order in which the department should be displayed
• 'level': integer value representing the level of the department in the hierarchy

The function first inserts all the top-level departments (those with 'supdepid = 0') into the table variable '@t'. It then enters a loop where it keeps inserting child departments into '@t' until there are no more child departments left to insert.

The loop works as follows:

• For each row in '@t', it selects all the child departments (those with 'supdepid' equal to the current row's 'id') that have not already been inserted into '@t'.
• For each child department, it constructs a new row to be inserted into '@t' with the same values as the child department, except with an updated 'code' and 'level' values. The 'code' is constructed by concatenating the 'code' of the current row with the 'id' of the child department, separated by an underscore. The 'level' is just the 'level' of the current row incremented by 1.
• The loop continues until there are no more child departments left to insert.

Finally, the function returns the table variable '@t'.