title: Get value from the select statement on the row above in T-SQL
body: <p>I want to get the value the previous select statement returned (similar to the <code>@@IDENTITY</code> function)</p>
  
  <p>Code:</p>
  
  <pre><code>IF EXISTS (SELECT MyCriticalValue
             FROM Units u
             JOIN pUnit pu
               ON pu.UnitID = u.UnitID
             WHERE u.ChildUnitID = ( SELECT ps.UnitID
                                     FROM psUnit ps
                                     WHERE ps.Code = @CurrentCode)
              AND pu.Type = 117
              AND u.TypeID = 1 )
  BEGIN
    SET @CriticalValue = @@identity
  END
  </code></pre>
  
  <p>I know that <code>@@IDENTITY</code> doesn't work since that's only for inserts. Is there a similar function to achieve what I want (which is to get "MyCriticalValue" from the select statement into the variable <code>@CriticalValue</code> without typing the entire SELECT statement twice).</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Clarification: When I come to this point in my SQL script <code>@CriticalValue</code> is already set and I should ONLY overwrite it if the select-statement returns anything, so it should not be overwritten in any other case.</p>