在SQL Server 2008的“From”子句中使用表名称的变量

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中实现这一点?

6 个解决方案

#1

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

#2

SET @SQL = 'SELECT * FROM ' + @table
EXEC (@SQL)  -- parentheses are required

#3

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

您可以UNION所有表并将表名作为列包含在内,然后将表名指定为谓词。如果您检查此示例的查询计划,则会看到未触及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'

#4

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。我以前必须实现这个。这很漂亮。

#5

You cannot do it. What problem are you solving, there might be other solutions.

你不能做到。您解决了什么问题,可能还有其他解决方案。

#6

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。什么是适合你的情况很难说。

#1