How to dynamically choose which table (with same schema) to select from in stored procedure

Question

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...

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.
)

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...

CREATE PROCEDURE GetData
    @TableName varchar(100)

AS
BEGIN

    DECLARE @sql varchar(max)
    SET @sql = 'SELECT * FROM ' + @sql

    exec(@sql)

... 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.

Is there a better way to do this?

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 might be an option, but would be a last resort.

Answer 1

If you have a reasonably small number of tables you could combine them into a view and query that. Example:

create view vw_MyTables
AS
   SELECT 'table1' as tableName, * from table1
   UNION
   SELECT 'table2', * from table2 
   UNION
   SELECT 'table3', * from table3 


SELECT * FROM vw_MyTables
WHERE tableName = @TableName

Otherwise I think your only option is dynamic sql...

If you are willing to change your schema around that would probably be best. Is there a reason you store identical(is it identical?) information for clients in different tables?

I have used the following structure to represent this type of data before:

Client
--------
ClientId - pk
ClientName


ClientData
-----------
DataId - pk
ClientId - fk to Client
Whatever

This is basically what my view does but if you do it this way you can create indexes on it and get your datas wicked fast.

Answer 2

If you absolutely have to separate the clients' data into separate tables, then I think the dynamic SQL is the best route. An alternative would be to have a view that selects from all client tables, like so:

create view AllClients as
select 'ClientOne' ClientName, c1.* from ClientOneData c1 union all
select 'ClientTwo' ClientName, c2.* from ClientTwoData c2 union all
.
.
.
etc.

- but this just replicates the single table that you have ruled out for security reasons, in a virtual form.

Answer 3

I'd combine the data into a single table and implement row-level security instead of separating into separate tables.