Reputation: 4607

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

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?

Upvotes: 6

Answers (6)

Cade Roux

Reputation: 89711

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.

Upvotes: 0

johnnycrash

Reputation: 5344

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

Upvotes: 21

A-K

Reputation: 17090

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

Upvotes: 0

ahains

Reputation: 1912

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'

Upvotes: 5