title: Alternative to function for dynamic SQL
body: <p>I have a stored procedure that returns a list of projectIDs at the moment. I want to extend this to return some extra information to determine if a specific user has access to this project.</p>
  
  <p>To determine if a user has access I need to look up the user in a table called 'Project_XXX' where XXX is the projectID. Yes, this is shocking database design but sadly there is nothing I can do about it. My first instinct was that I could use a function as such:</p>
  
  <pre><code>SELECT ProjectID WHERE [criteria] AND myfunc(ProjectID, @username)=1
  </code></pre>
  
  <p>However, because the lookup needs to run dynamic SQL it tells me that I can't do this.</p>
  
  <p>This leaves me with the option of putting my logic in a stored procedure but then the syntax of my select is not simple.</p>
  
  <p>The way I can think of to do it is to run the select to get the list of projectIDs and then run a cursor over that table calling my stored procedure but I stopped thinking there because it seems far too complicated...</p>
  
  <p>So is there some way I can do what I want with similarly simple syntax to above? Is there some way to get a user function to return based on dynamic SQL? Is there a nice way to use stored procedures as I have above? I'm sure I am missing something obvious - I rarely need to do anything complicated with SQL though. ;-)</p>
  
  <p>I should also reiterate that I <em>know</em> that the underlying problem is with the stupid individual project tables but this is not something that can be changed.</p>