title: sql parameterised cte query
body: <p>I have a query like the following</p>
  
  <pre><code>select  * 
  from        (
                 select   *
                 from     callTableFunction(@paramPrev)
                  .....&lt; a whole load of other joins, wheres , etc &gt;........
              ) prevValues
              full join
              (
                  select  *
                  from    callTableFunction(@paramCurr)
                  .....&lt; a whole load of other joins, wheres , etc &gt;........
              ) currValues                on prevValues.Field1 = currValues.Field1
              ....&lt;other joins with the same subselect as the above two with different parameters passed in
  where       ........
  group by    ....
  </code></pre>
  
  <p>The following subselect is common to all the subselects in the query bar the @param to the table function.</p>
  
  <pre><code>        select  *
          from    callTableFunction(@param)
              .....&lt; a whole load of other joins, wheres , etc &gt;........
  </code></pre>
  
  <p>One option is for me to convert this into a function and call the function, but i dont like this as I may be changing the 
  subselect query quite often for.....or I am wondering if there is an alternative using CTE
  like</p>
  
  <pre><code>with sometable(@param1) as 
  (
          select  *
          from    callTableFunction(@param)
                  .....&lt; a whole load of other joins, wheres , etc &gt;........
  )
  select      
          sometable(@paramPrev)       prevValues
          full join sometable(@currPrev)  currValues  on prevValues.Field1 = currValues.Field1
  where       ........
  group by    ....
  </code></pre>
  
  <p>Is there any syntax like this or technique I can use like this.</p>
  
  <p>This is in SQL Server 2008 R2</p>
  
  <p>Thanks.</p>