title: How to reuse result of calculated field in a view
body: <p>I have a view contstructed like this:</p>
  
  <pre><code>CREATE VIEW [dbo].[myview] AS
  SELECT   
         dbo.table1.field1
        ,dbo.table2.field2  
        ,dbo.HeavyStoredProcedure(dbo.table1.field1) AS calculated_value 
        ...
  FROM
        table1
        INNER JOIN table2 ON (...)
  WHERE  
        ...
        AND NOT( EXISTS (SELECT * from table3 
                 WHERE (table3.somefield = dbo.HeavyStoredProcedure(dbo.table1.field1)))
                 OR
                 EXISTS (SELECT * from table4
                 WHERE (table4.anotherfield = dbo.HeavyStoredProcedure(dbo.table1.field1)))
               )
  </code></pre>
  
  <p>The problem here is that the <strong>"HeavyStoredProcedure"</strong> gets called numerous times with the <em>exact same argument</em>, thus slowing down the whole thing to a crawl.</p>
  
  <p>How can I make my view just call <strong>"HeavyStoredProcedure"</strong> once per row in table1 and then reuse the calculated result twice in the WHERE clause?</p>