How do I supply the FROM clause of a SELECT statement from a UDF parameter

Question

In the application I'm working on porting to the web, we currently dynamically access different tables at runtime from run to run, based on a "template" string that is specified. I would like to move the burden of doing that back to the database now that we are moving to SQL server, so I don't have to mess with a dynamic GridView. I thought of writing a Table-valued UDF with a parameter for the table name and one for the query WHERE clause.

I entered the following for my UDF but obviously it doesn't work. Is there any way to take a varchar or string of some kind and get a table reference that can work in the FROM clause?

CREATE FUNCTION TemplateSelector 
(   
@template varchar(40),
@code varchar(80)
)
RETURNS TABLE 
AS
RETURN 
(
SELECT * FROM @template WHERE ProductionCode = @code
)

Or some other way of getting a result set similar in concept to this. Basically all records in the table indicated by the varchar @template with the matching ProductionCode of the @code.

I get the error "Must declare the table variable "@template"", so SQL server probably things I'm trying to select from a table variable.

On Edit: Yeah I don't need to do it in a function, I can run Stored Procs, I've just not written any of them before.

Answer 1

The only way to do this is with the exec command.

Also, you have to move it out to a stored proc instead of a function. Apparently functions can't execute dynamic sql.

Answer 2

CREATE PROCEDURE TemplateSelector 
(   
    @template varchar(40),
    @code varchar(80)
)

AS
EXEC('SELECT * FROM ' + @template + ' WHERE ProductionCode = ' + @code)

This works, though it's not a UDF.

Answer 3

The only way that this would be possible is with dynamic SQL, however, dynamic SQL is not supported by SqlServer within a function.

I'm sorry to say that I'm quite sure that it is NOT possible to do this within a function.

If you were working with stored procedures it would be possible.

Answer 4

Also, it should be noted that, be replacing the table name in the query, you've destroyed SQL Server's ability to cache the execution plan for the query. This pretty much reduces the advantage of using a UDF or SP to nil. You might as well just call the SQL query directly.

Answer 5

I have a finite number of tables that I want to be able to address, so I could writing something using IF, that tests @template for matches with a number of values and for each match runs

SELECT * FROM TEMPLATENAME WHERE ProductionCode = @code

It sounds like that is a better option

Answer 6

If you have numerous tables with identical structure, it usually means you haven't designed your database in a normal form. You should unify these into one table. You may need to give this table one more attribute column to distinguish the data sets.