Just wanted to throw in a quick write up about common table expressions (CTE) in sql server.

There are plenty of good and complete write-ups on ctes floating around teh tubes these days but I wanted to address my paticular issue, if nothing else so I don’t forget about them.

Imagine you have a hiearchy of units (like military units) though this same thing can apply to other cases like attributes or types. Working with some made-up data you may have:

5th Division
9th Brigade
17th Regiment
21st Regiment
12 Brigade
54th Regiment
88th Regiment

I have a case where a user will be assigned to just one level of these units but being assigned to one level means that all child units also apply to the user.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[_Unit_GetByUserId]
(
     @UserId [uniqueidentifier]
)
AS

     with c as (
          select b.* from Unit b, UserUnit u 
          where u.UserId = @UserId 
          and b.Id = u.UnitId
          union all
          select b.* from Unit b 
          join c on b.ParentId = c.Id 
          where b.Id != b.ParentId
     )
	
     select * from c

This allows me to get all the units that apply to the user. In this case, if a user is assigned to the 9th Brigade then the stored proc will return: 9th Brigade, 17th Regiment, 21st Regiment

The key is in the union where the join to the CTE is and the ParentId is being set to the Id coming from the CTE.

I realize there isn’t much explanation here as to what is happening but if you follow the links above you’ll get a better explanation then I could give.