問題描述

在我正在移植到 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 子句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！