title: Reusing computed values within the same SELECT
body: <h2>Background</h2>
  <p>I'm maintaining a complex stored procedure for, essentially, committing staged changes to a final inventory. There are a couple of linked fields in what it does, as:</p>
  <pre><code>INSERT INTO (itemNumber, itemCode) ' and a lot more columns
  SELECT stageNumber, Inventory.ConstructItemCode(stageNumber) ' and a lot more columns
  FROM Inventory.Staging WHERE state = @final
  </code></pre>
  <h2>Problem</h2>
  <p>Our process for determining the final item number is going to get a lot more complex. I don't want to do:</p>
  <pre><code>INSERT INTO (itemNumber, itemCode)
  SELECT *a complicated case statement*, Inventory.ConstructItemCode(*the same complicated case statement*)
  FROM Inventory.Staging WHERE state = @final
  </code></pre>
  <p>because of duplication concerns, and while it's possible to do this:</p>
  <pre><code>INSERT INTO (itemNumber, itemCode)
  SELECT i.caseNumber, Inventory.ConstructItemCode(i.caseNumber)
  FROM (SELECT *a complicated case statement* as caseNumber 
        FROM Inventory.Staging WHERE state = @final) i
  </code></pre>
  <p>I'd also have to stick all the other columns I needed into the intermediate select, and properly qualify them on the way out, right? Is there a way to remember the result of the case statement and use it again within the same select, or am I stuck bubbling everything through that intermediate select statement?</p>