title: Can you cache the results of a subquery that's used repeatedly in a stored procedure?
body: <p>I have a series of queries being <code>UNION</code>'d together. Each query has a <code>WHERE... IN</code> clause that compares against the same list of IDs.</p>
  
  <p>In a simplified form for example purposes it looks like this:</p>
  
  <pre><code>SELECT * FROM MyTable
  WHERE AuthorUserId IN (SELECT UserId FROM Users WHERE TeamId = @teamId)
  
  UNION
  
  SELECT * FROM MyTable
  WHERE PublisherUserId IN (SELECT UserId FROM USERS WHERE TeamId = @teamId)
  
  UNION...
  </code></pre>
  
  <p>and so on. <code>@teamId</code> is an int stored procedure parameter.</p>
  
  <p>Is there a way to tell SQL Server to hold on to the result set of</p>
  
  <pre><code>SELECT UserId FROM USERS WHERE TeamId = @teamId
  </code></pre>
  
  <p>so it doesn't fetch it for each <code>SELECT</code>?</p>