如何动态选择在存储过程中选择哪个表（具有相同的模式）

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

我想要一个存储过程从适当的表中检索数据。我可以通过将表名作为参数传递给proc然后构建一个sql字符串来执行...


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.

我非常乐于接受建议,从我在proc中可以做的事情到重新处理数据模型(没有将所有数据转储到单个表中)。升级到SQL-Server 2008或2010可能是一种选择,但这将是最后的选择。

4 个解决方案

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

否则我认为你唯一的选择是动态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.

这基本上就是我的观点所做的,但如果你这样做,就可以在其上创建索引并快速获取数据。

#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:

如果您必须将客户端的数据分成单独的表,那么我认为动态SQL是最佳路由。另一种方法是从所有客户端表中选择一个视图,如下所示:

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.

- 但这只是复制了您出于安全原因排除的单个表格,以虚拟形式。

#3

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

我将数据组合到一个表中并实现行级安全性,而不是分成单独的表。

#4

Dynamic SQL using a parameter verbatim is a security hole. Someone could exploit your sproc and pass a drop command, for instance.

使用参数逐字的动态SQL是一个安全漏洞。例如,有人可以利用你的sproc并传递drop命令。

Also a sproc with Dynamic SQL will recompile each time it is executed.

使用Dynamic SQL的sproc也会在每次执行时重新编译。

A sproc using Abe Miessler's view would be the proper way to do it: secured and not recompiled.

使用Abe Miessler的观点的sproc将是正确的方法:安全和不重新编译。

#1