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.