SQL ServerSelect

SQL Server - Select - CTE

Common Table Expression

A Common Table Expression (aka CTE) is fairly new to SQL server. It’s very similar to a subquery, or a #TempTable however it can be used to implement solutions recursively.

Here is an example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
with DirectReports(Name_LFM, NtUserName, SupvNtUserName 
                                  , SupvName_LFM, Level, SupvEspn, block)  
as  
(   /* returns a record for each person this person supervises*/ 
       SELECT EmpName_LFM=e.empLname+', '+e.empFname+' '+isnull(e.empmi,'')+' '+isnull(e.empNameSuffix,'')   
              , ntUserName=upper(e.empUserName)   
              , SupvNtUserName=upper(cast(e.Supervisor_NtUserName as varchar))  
              , SupvName_LFM=s.empLname+', '+s.empFname+' '+isnull(s.empmi,'')+' '+isnull(s.empNameSuffix,'')   
              , level = 0, SupvEspn=s.espn   
              , block='IsASupervisor' 
       from employees e   
       join employees as s on e.supervisor_ntUsername=s.empUserName  
       where e.Supervisor_NtUserName='tsTestSo'   
       AND e.cdempstatus < 99  
       union all  
       /* the supervisor of the person we are looking at */ 
       SELECT Name_LFM=e1.empLname+', '+e1.empFname+' '+isnull(e1.empmi,'')+' '+isnull(e1.empNameSuffix,'')   
              , ntUserName=upper(e1.empUserName)   
              , SupvNtUserName=upper(cast(e1.Supervisor_NtUserName as varchar))  
              , SupvName_LFM=s1.empLname+', '+s1.empFname+' '+isnull(s1.empmi,'')+' '+isnull(s1.empNameSuffix,'')   
              , level = 0, s1.espn  
              , block='IsHisSupervisor' 
       from employees e1  
       join employees as s1 on e1.supervisor_ntUsername=s1.empUserName  
       where e1.EmpUserName='tsTestSo'   
       AND e1.cdempstatus < 99  
       union all  
       /* Compute backup Supervisors */  
       SELECT Name_LFM=e1.empLname+', '+e1.empFname+' '+isnull(e1.empmi,'')+' '+isnull(e1.empNameSuffix,'')   
           , ntUserName=upper(e1.empUserName)   
           , SupvNtUserName=upper(cast(s1.empUserName as varchar))  
           , SupvName_LFM=s1.empLname+', '+s1.empFname+' '+isnull(s1.empmi,'')+' '+isnull(s1.empNameSuffix,'')  
           , level = 0, s1.espn , block='IsABackupFor'  
        from employees e1  
       join SupvBackups as sp on e1.espn=sp.espn  
       join employees as s1 on sp.BackupESPN=s1.espn  
       where e1.EmpUserName='tsTestSo' 
       union all  
    /* recursive stuff - yuck! - employee of one of the others*/ 
       SELECT Name_LFM=emp.empLname+', '+emp.empFname+' '+isnull(emp.empmi,'')+' '+isnull(emp.empNameSuffix,'')   
              , ntUserName=upper(emp.empUserName)   
              , SupvNtUserName=upper(cast(emp.Supervisor_NtUserName as varchar))  
              , SupvName_LFM=s.empLname+', '+s.empFname+' '+isnull(s.empmi,'')+' '+isnull(s.empNameSuffix,'')   
              , level +1, s.espn  
              , block='IsASupOfAnother' 
       from employees emp  
       join employees as s on emp.supervisor_ntUsername=s.empUserName  
       inner join DirectReports as e   
              on emp.Supervisor_NtUserName=e.ntUserName  
     and e.NtUserName<>e.SupvNtUserName  
       where emp.cdempstatus < 99  
    and level<10  
)  
select distinct NtUserName=SupvNtUserName, Name_LFM=SupvName_LFM, espn=SupvEspn /*, block */ 
/*select distinct Name_LFM, NtUserName, SupvNtUserName, SupvName_LFM, Level, dr.SupvEspn, block */ 
from DirectReports dr 
order by supvntUserName

This is a SQL statement which is used to populate a dropdown containing supervisors. Most specifically, my supervisor, my backup supervisors, and employees who are supervisors of other people associated with the people I selected initially.

The CTE statement starts with With ASubQueryName (output parameters) as (subquery here).

Then you follow that CTE with a query that will call the CTE.

As you see within the CTE, a union query, and the forth SQL statement is joining in that CTE - thus the recursiveness. For me this is complex.

One thing I don’t particularly care about, is that you can only use that CTE in the next statement, I found to debug this I end up enabling/ and disabling my actual output with another select that gives me information used to trace out problems.