Clever Geek Handbook

How can I provide the FROM clause of a SELECT statement from a UDF parameter

In the web porting application I am currently working on, we are currently dynamically accessing various tables at runtime from startup to startup based on a specified "wildcard" string. I would like to shift this burden to get back to the database now that we are moving to SQL Server, so I don't need to mess with the dynamic GridView. I was thinking about writing a table-valued UDF with a parameter for the table name and one for the WHERE clause.

I entered the following for my UDF, but obviously it doesn't work. Is there a way to take a varchar or string of some type and get a table reference that can work in a 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 to get a result set similar to this concept. Basically all records in the table are specified with varchar @template with corresponding production @code.

I am getting the error "Must declare table variable '@template'", so the SQL server is probably all I am trying to fetch from the table.

In the editor: Yes, I don't need to do this in a function, I can run stored proxies, I haven't written them yet.

+1


source to share


6 answers 


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

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


This works, although it is not a UDF.

+4


source


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



Also, you need to move it to the stored proc instead of the function. Apparently the functions cannot do dynamic sql.

+2


source


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

I'm sorry that I'm pretty sure it can't be done within a function.

If you've worked with stored procedures, this would be possible.

0


source


Also, it should be noted that by replacing the table name in the query, you destroyed SQL Server's ability to cache the query execution plan. This greatly reduces the advantage of using UDF or SP for nil. You can just call the SQL query directly.

0


source


I have a finite number of tables that I want to be able to address, so I could write something using IF that @template tests for matches to a range of values ​​and for each match is done

SELECT * FROM TEMPLATENAME WHERE ProductionCode = @code

It looks like this is the best option

0


source


If you have many tables with the same structure, this usually means that you have not created your database in normal form. You have to combine them into one table. You may need to provide another attribute column to this table to differentiate datasets.

0


source






More articles:


All Articles