title: Alternatives to large IN clause in SQL Server
body: <p>I am currently refactoring a function which builds our custom WHERE clause from supplied search criteria. The one issue I have come across is user access to projects. I receive, from the claims service, a <code>List&lt;long&gt;</code> of all the project IDs a user has access to. Currently this is formatted as an IN clause:</p>
  
  <pre><code>SELECT * FROM items WHERE project_id IN (1, 2, 3, 4)
  </code></pre>
  
  <p>As the system scales this will balloon to possibly many thousands of IDs. I have researched how to avoid the IN clause, but mostly people use a stored procedure or create a temporary table and join to that. As of right now I have to use the claims service, and my list of IDs.</p>
  
  <p>Is there a better way to do this, given my constraints? If not, I can escalate the issue to do a larger refactor. Thanks in advance.</p>