Here's the situation. Due to the design of the database I have to work with, I need to write a stored procedure in such a way that I can pass in the name of the table to be queried against if at all possible. The program in question does its processing by jobs, and each job gets its own table created in the database, IE table-jobid1, table-jobid2, table-jobid3, etc. Unfortunately, there's nothing I can do about this design - I'm stuck with it.
这是情况。由于我必须使用的数据库的设计,我需要编写一个存储过程,以便我可以传递要查询的表的名称,如果可能的话。有问题的程序按作业进行处理,每个作业都在数据库中创建自己的表,IE table-jobid1,table-jobid2,table-jobid3等。不幸的是,我对这个设计无能为力 - 我我坚持了下来。
However, now, I need to do data mining against these individualized tables. I'd like to avoid doing the SQL in the code files at all costs if possible. Ideally, I'd like to have a stored procedure similar to:
但是,现在,我需要针对这些个性化表进行数据挖掘。如果可能的话,我想不惜一切代价避免在代码文件中执行SQL。理想情况下,我希望有一个类似于的存储过程:
SELECT *
FROM @TableName AS tbl
WHERE @Filter
Is this even possible in SQL Server 2005? Any help or suggestions would be greatly appreciated. Alternate ways to keep the SQL out of the code behind would be welcome too, if this isn't possible.
这在SQL Server 2005中是否可行?任何帮助或建议将不胜感激。如果不可能的话,也欢迎将SQL排除在代码背后的替代方法。
Thanks for your time.
谢谢你的时间。
3 个解决方案
#1
best solution I can think of is to build your sql in the stored proc such as:
我能想到的最好的解决方案是在存储过程中构建你的sql,例如:
@query = 'SELECT * FROM ' + @TableName + ' as tbl WHERE ' + @Filter
@query ='SELECT * FROM'+ @TableName +'as tbl WHERE'+ @Filter
exec(@query)
not an ideal solution probably, but it works.
可能不是一个理想的解决方案,但它有效。
#2
The best answer I can think of is to build a view that unions all the tables together, with an id column in the view telling you where the data in the view came from. Then you can simply pass that id into a stored proc which will go against the view. This is assuming that the tables you are looking at all have identical schema.
我能想到的最好的答案是构建一个将所有表联合在一起的视图,视图中的id列告诉您视图中的数据来自何处。然后你可以简单地将该id传递给存储过程,该过程将违背视图。这假设您正在查看的表具有相同的模式。
example:
create view test1 as
select * , 'tbl1' as src
from job-1
union all
select * , 'tbl2' as src
from job-2
union all
select * , 'tbl3' as src
from job-3
Now you can select * from test1 where src = 'tbl3' and you will only get records from the table job-3
现在你可以从test1中选择*,其中src ='tbl3',你只能从表job-3中获取记录
#3
This would be a meaningless stored proc. Select from some table using some parameters? You are basically defining the entire query again in whatever you are using to call this proc, so you may as well generate the sql yourself.
这将是一个毫无意义的存储过程。使用一些参数从某些表中选择?您基本上是在用于调用此proc的任何内容中再次定义整个查询,因此您也可以自己生成sql。
the only reason I would do a dynamic sql writing proc is if you want to do something that you can change without redeploying your codebase. But, in this case, you are just SELECT *'ing. You can't define the columns, where clause, or order by differently since you are trying to use it for multiple tables, so there is no meaningful change you could make to it.
我想做动态sql写入过程的唯一原因是你想要做一些你可以改变而不需要重新部署代码库的东西。但是,在这种情况下,你只是SELECT *'ing。您不能以不同方式定义列,where子句或顺序,因为您尝试将其用于多个表,因此您无法对其进行任何有意义的更改。
In short: it's not even worth doing. Just slop down your table specific sprocs or write your sql in strings (but make sure it's parameterized) in your code.
简而言之:它甚至不值得做。只需在表格中删除特定的sprocs,或者在代码中用字符串编写sql(但要确保它已参数化)。
#1
best solution I can think of is to build your sql in the stored proc such as:
我能想到的最好的解决方案是在存储过程中构建你的sql,例如:
@query = 'SELECT * FROM ' + @TableName + ' as tbl WHERE ' + @Filter
@query ='SELECT * FROM'+ @TableName +'as tbl WHERE'+ @Filter
exec(@query)
not an ideal solution probably, but it works.
可能不是一个理想的解决方案,但它有效。
#2
The best answer I can think of is to build a view that unions all the tables together, with an id column in the view telling you where the data in the view came from. Then you can simply pass that id into a stored proc which will go against the view. This is assuming that the tables you are looking at all have identical schema.
我能想到的最好的答案是构建一个将所有表联合在一起的视图,视图中的id列告诉您视图中的数据来自何处。然后你可以简单地将该id传递给存储过程,该过程将违背视图。这假设您正在查看的表具有相同的模式。
example:
create view test1 as
select * , 'tbl1' as src
from job-1
union all
select * , 'tbl2' as src
from job-2
union all
select * , 'tbl3' as src
from job-3
Now you can select * from test1 where src = 'tbl3' and you will only get records from the table job-3
现在你可以从test1中选择*,其中src ='tbl3',你只能从表job-3中获取记录
#3
This would be a meaningless stored proc. Select from some table using some parameters? You are basically defining the entire query again in whatever you are using to call this proc, so you may as well generate the sql yourself.
这将是一个毫无意义的存储过程。使用一些参数从某些表中选择?您基本上是在用于调用此proc的任何内容中再次定义整个查询,因此您也可以自己生成sql。
the only reason I would do a dynamic sql writing proc is if you want to do something that you can change without redeploying your codebase. But, in this case, you are just SELECT *'ing. You can't define the columns, where clause, or order by differently since you are trying to use it for multiple tables, so there is no meaningful change you could make to it.
我想做动态sql写入过程的唯一原因是你想要做一些你可以改变而不需要重新部署代码库的东西。但是,在这种情况下,你只是SELECT *'ing。您不能以不同方式定义列,where子句或顺序,因为您尝试将其用于多个表,因此您无法对其进行任何有意义的更改。
In short: it's not even worth doing. Just slop down your table specific sprocs or write your sql in strings (but make sure it's parameterized) in your code.
简而言之:它甚至不值得做。只需在表格中删除特定的sprocs,或者在代码中用字符串编写sql(但要确保它已参数化)。