自定义函数：
1 USE [ExpenseCenter_Fibrogen]
2 GO
3 /****** Object: UserDefinedFunction [dbo].[GetSubordinateTable] Script Date: 2014/10/11 13:24:32 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER FUNCTION [dbo].[GetSubordinateTable]
9 (
10 @adaccount nvarchar(128),
11 @includeResign bit,
12 @allowMore bit
13 )
14 RETURNS @SubordinateTable TABLE
15 (
16 ADAccount nvarchar(128),
17 ChineseName nvarchar(128),
18 EnglishName nvarchar(128)
19 )
20 AS
21 Begin
22
23 Insert Into @SubordinateTable
24 Select ADAccount,ChineseName,EnglishName
25 From SystemUser Where ReportingUserADAccount = @adaccount
26 And (@includeResign = 1 Or IsActive=1)
27
28 if @allowMore=1
29 Begin
30 declare @acc nvarchar(128)
31 set @acc = ''
32 while 1=1
33 Begin
34 Select Top 1 @acc = ADAccount From SystemUser Where ReportingUserADAccount = @adaccount And (@includeResign = 1 Or IsActive=1)
35 And ADAccount>@acc Order By ADAccount
36
37 if @@ROWCOUNT=0
38 break
39
40 Insert Into @SubordinateTable
41 Select * From GetSubordinateTable(@acc,@includeResign,@allowMore)
42 End
43 End
44
45 RETURN
46 End
WITH函数（仅支持SQL SERVE 2008）
向上查找
1 WITH Users(ADAccount,ParentADAccount)
2 as
3 (
4 SELECT ADAccount,ReportingUserADAccount FROM SystemUser where ADAccount='fli'
5 UNION ALL
6 SELECT A.ADAccount,ReportingUserADAccount FROM SystemUser A,Users b
7 where a.ADAccount = b.ParentADAccount
8 )
9 select * from Users
向下查找
1 WITH Users(ADAccount,ParentADAccount)
2 as
3 (
4 SELECT ADAccount,ReportingUserADAccount FROM SystemUser where ADAccount='xwang'
5 UNION ALL
6 SELECT A.ADAccount,ReportingUserADAccount FROM SystemUser A,Users b
7 where a.ReportingUserADAccount = b.ADAccount
8 )
9 select * from Users
