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

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.

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.

GMTA! – Mark Bannister Oct 25 '10 at 17:07 Indeed they do ;) – Abe Miessler Oct 25 '10 at 17:09.

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.

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.