在 SQL Server 2008 的“From”子句中使用表名变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/988610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a variable for table name in 'From' clause in SQL Server 2008
提问by DaveK
I have a UDF that queries data out of a table. The table, however, needs to be definable as a parameter. For example I can't have:
我有一个从表中查询数据的 UDF。但是，该表需要可定义为参数。例如我不能有：
Select * From [dbo].[TableA]
选择 * 从 [dbo].[TableA]
I need something like:
我需要类似的东西：
Select * From [dbo].[@TableName]
选择 * 从 [dbo].[@TableName]
The above line doesn't work, and also the UDF prohibits me from setting the query as a string and calling exec(). I can do this in a procedure, but I can't call the procedure from the UDF either.
上面的行不起作用，而且 UDF 也禁止我将查询设置为字符串并调用 exec()。我可以在过程中执行此操作，但也无法从 UDF 调用该过程。
Does anyone know how I can accomplish this within the UDF without having some kind of massive switch statement?
有谁知道如何在 UDF 中完成此操作而无需某种大量的 switch 语句？
回答by johnnycrash
SET @SQL = 'SELECT * FROM ' + @table
EXEC (@SQL) -- parentheses are required
回答by Andomar
This can't be done, dynamic SQL is not supported in functions.
这是做不到的，函数中不支持动态 SQL。
See this SO question: Executing dynamic SQL in a SQLServer 2005 function
请参阅此 SO 问题： 在 SQLServer 2005 函数中执行动态 SQL
回答by ahains
You can UNION ALL your tables and include the table name as a column, then specify the table name as a predicate over this. If you check the query plan for this example you see that t2 is not touched
您可以联合所有表并将表名作为一列包含在内，然后将表名指定为对此的谓词。如果您检查此示例的查询计划，您会看到未触及 t2
create table t1 (i int)
create table t2 (i int)
insert t1 values(1)
insert t1 values(2)
insert t1 values(3)
insert t2 values(4)
insert t2 values(5)
insert t2 values(6)
;with tableSet as (
select i, 't1' as tableName from t1
union all select i, 't2' as tableName from t2
)
select i from tableSet where tableName = 't1'
回答by Justin Balvanz
You can write a udf clr that can do dynamic sql. I've had to implement this before. It's pretty slick.
你可以写一个可以做动态sql的udf clr。我以前必须实现这一点。它很光滑。
回答by A-K
You cannot do it. What problem are you solving, there might be other solutions.
你做不到。你在解决什么问题，可能还有其他解决方案。
回答by Cade Roux
If you would give more details about what underlying problem you are trying to solve, we might have better answers. One solution is to code generate the UDFs on a per-table basis. Another is to use dynamic SQL from an SP. What's right for your situation is hard to say.
如果您能提供更多有关您要解决的潜在问题的详细信息，我们可能会有更好的答案。一种解决方案是在每个表的基础上编码生成 UDF。另一种方法是使用来自 SP 的动态 SQL。很难说哪种适合您的情况。
