Using a variable for table name in 'From' clause in SQL Server 2008

Question

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:

Select * From [dbo].[TableA]

I need something like:

Select * From [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.

Does anyone know how I can accomplish this within the UDF without having some kind of massive switch statement?

Answer 1

This can't be done, dynamic SQL is not supported in functions.

See this SO question: http://stackoverflow.com/questions/150552/executing-dynamic-sql-in-a-sqlserver-2005-function

Answer 2

You can write a udf clr that can do dynamic sql. I've had to implement this before. It's pretty slick.

Answer 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

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'

Answer 4

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

Answer 5

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

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