title: How to dynamically choose which table (with same schema) to select from in stored procedure
body: <p>I have a bit of an odd database model for a project - I'm going to have several tables with the same definition.  Each table stores information for different clients and because of security restrictions, we cannot put the data together in one table.  So it would look something like this...</p>
  
  <pre><code>
  table ClientOneData
  (
      Id (PK, int, not null),
      Col1 (varchar(50), not null),
      etc.
  )
  
  table ClientTwoData
  (
      Id (PK, int, not null),
      Col1 (varchar(50), not null),
      etc.
  )
  </code></pre> 
  
  <p>I want a single stored procedure to retrieve data from the appropriate table. I could do that by just passing the table name as a param to the proc and then building up a string of sql to execute...</p>
  
  <pre><code>
  CREATE PROCEDURE GetData
      @TableName varchar(100)
  
  AS
  BEGIN
  
      DECLARE @sql varchar(max)
      SET @sql = 'SELECT * FROM ' + @sql
  
      exec(@sql)
  </code></pre>
  
  <p>... but that seems fundamentally wrong to me.  And replicating code, either in the form of a giant case statement or by "one-offing" the proc and creating a new one for each client, also seems wrong.</p>
  
  <p>Is there a better way to do this? </p>
  
  <p>I'm pretty open to suggestions, anything from something I can do in the proc to re-working the data model (short of dumping all the data into a single table).  Upgrading to SQL-Server 2008 or 2010 <em>might</em> be an option, but would be a last resort.</p>