如何从 UDF 参数提供 SELECT 语句的 FROM 子句 [英] How do I supply the FROM clause of a SELECT statement from a UDF parameter

问题描述

在我正在移植到 Web 的应用程序中，我们目前在运行时根据指定的模板"字符串在运行时动态访问不同的表.既然我们要迁移到 SQL 服务器，我想将执行此操作的负担移回数据库，这样我就不必弄乱动态 GridView.我想写一个表值 UDF，其中一个参数用于表名，一个用于查询 WHERE 子句.

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.

我为我的 UDF 输入了以下内容，但显然它不起作用.有没有办法获取某种类型的 varchar 或字符串并获得可以在 FROM 子句中工作的表引用?

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
)

或者其他一些获得与此概念类似的结果集的方法.基本上表中的所有记录都由 varchar @template 和 @code 的匹配 ProductionCode 指示.

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.

我收到错误必须声明表变量@template""，因此 SQL Server 可能是我试图从表变量中选择的内容.

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

推荐答案

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

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

这有效，虽然它不是 UDF.

This works, though it's not a UDF.

这篇关于如何从 UDF 参数提供 SELECT 语句的 FROM 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！