title: SQL - Assignment in CASE inside a SELECT in a Recursive Query
body: <p>I'm creating a SQL Server 2008 query that would output the list of employees in a company along with the team they are on with an additional column. </p>
  
  <p>Example of the org tree: </p>
  
  <pre><code>Level 0: CEO
  Level 1: A, B, and C
  Level 2:
      For A:1,2,3
      For B:4,5,6
      For C:7,8,9
  </code></pre>
  
  <p>In my resulting set, I should see three columns -- name, level (of the tree), and team. For 1,2, and 3, I'd see 'A' as their team and 2 as the level. For 4,5, and 6, 'B' and 2 for the level and so on.</p>
  
  <p>I'm using a recursive query to navigate the tree (no problems there), but since I need to "carry" the team name down the query (in case there's a level 8 -- it should still show the person in level 1 they report to), I'm doing this:</p>
  
  <pre><code>(...)
  UNION ALL
  -- Recursive Member Definition
  -- in here level increments one each time, and the team should output the child
  -- of the top manager
  SELECT  A.treenodeid, A.parentnodeid, A.email, LEVEL+1, team = 
                                      CASE LEVEL
                                          When 1 then SET @salead = A.Email
                                          Else @salead
                                      END
     FROM XX as A 
    INNER JOIN TeamsTable as B on A.parentnodeid = b.treenodeID
  </code></pre>
  
  <p>Since <strong>I'm trying to use a CASE to check if the level is 1</strong> (to update the team name to whatever the team lead's email name is), SQL keeps saying that in the case I have "<strong>Incorrect syntax near SET</strong>".</p>
  
  <p>Is it possible to do this sort of assignment in a CASE? I've looked around and haven't found if this can work with my recursive case.</p>
  
  <p>Here's all the query (assuming that the root is 'JohnSmith'):</p>
  
  <pre><code>WITH TeamsTable (treenodeid, parentnodeid, email, Level, team)
  AS
  ( 
      -- Anchor - Level starts with 0, and the team is empty for the top manager
      SELECT treenodeid,parentnodeid,email,0,''       
       FROM XX WHERE email = 'JohnSmith'
  
      UNION ALL
  
      -- Recursive Member Definition - in here level increments one each time, and the team should output the child of the top manager
      SELECT
          A.treenodeid, A.parentnodeid, A.email, LEVEL+1, team = 
                                  CASE LEVEL
                                      When 1 then SET @salead = A.Email
                                      Else @salead
                                  END
      FROM XX as A 
      INNER JOIN TeamsTable as B on A.parentnodeid = b.treenodeID
  )
  
  -- Statement that executes the CTE
  SELECT *
  FROM TeamsTable
  </code></pre>
  
  <p>Thanks a lot, guys!</p>