title: Multiple Select Statements using SQL Server 2005 "WITH" Statement
body: <p>I am trying to use the "WITH" statement in SQL Server 2005. It seems to work fine if I do something like:</p>
  
  <pre><code>WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')
  SELECT Name FROM MyBigProducts
  </code></pre>
  
  <p>But it fails if I try to use multiple select statements like:</p>
  
  <pre><code>WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')
  SELECT Name FROM MyBigProducts
  SELECT Count(*) FROM MyBigProducts
  </code></pre>
  
  <p>and the error message is "Invalid object name 'MyBigProducts'".</p>
  
  <p>Is there something I can do to increase the scope of the "MyBigProducts" table to include both of the select statements?</p>
  
  <p>My example is a simplification of my actual code causing the problem, so I'm not discounting the possibility that the above trivial example <em>should</em> work and that there is another bug in my SQL.</p>
  
  <p>I have tried wrapping a BEGIN and END around the two SELECT statements, but the parser could not compile it.</p>