SQL Server:將表名作為參數傳遞給表值函式
我所有的表都有這兩列:
[Id] [uniqueidentifier] NOT NULL [Revision] [bigint] IDENTITY(1, 1) NOT NULL
每隔一次我寫一個查詢,我想消除一些行。每張桌子的淘汰條件都是一樣的。我想要一種簡單的方法來指定這些條件一次,然後在任何表上重用它們。
我將表類型定義為我的表的“基類”
CREATE TYPE EntityTable AS TABLE ( [Id] [uniqueidentifier] NOT NULL ,[Revision] [bigint] IDENTITY(1, 1) NOT NULL )
這是我創建的表值函式
CREATE FUNCTION MaxRevision (@Entities EntityTable READONLY) RETURNS TABLE AS RETURN SELECT * FROM @Entities e WHERE e.Revision = ( SELECT max(Revision) FROM @Entities er WHERE er.id = e.id )
我以為我可以這樣使用它
SELECT * FROM MaxRevision(NameOfSomeTableWhichHasIdAndRevisionColumn) WHERE /* some other conditions */
但我得到了錯誤
Msg 207, Level 16, State 1, Line 2 Invalid column name 'NameOfSomeTableWhichHasIdAndRevisionColumn'.
為什麼它將我傳遞的表名視為列名?還有其他方法可以實現我想要的行為嗎?
表值參數 (TVP) 和一般的 T-SQL 不能那樣工作。無法將部分模式定義為介面,然後傳入與該介面匹配的表。對於 TVP,您需要傳入從該表類型創建的表變數。所以顯然不是去這裡的路。
此外,您已經說過所有這些表都具有這兩個列,但並不是所有這些表都具有所有相同的列。這是一個重要的區別,因為表值函式需要返回一致的結果集;結果集不能像儲存過程那樣是動態的。我之所以提到這一點,是因為您的範例函式
SELECT *
從 TVP 執行,這意味著您可能希望此函式返回您傳入的任何表名中的任何列。對於此函式,您只需要返回所有的兩列的表有共同點:Id
和Revision
。話雖如此,有兩種方法可以實現這一點:純 T-SQL 和 SQLCLR。
純 T-SQL
該函式使用一條
CASE
語句來確定要執行的子查詢。除了表名之外,每個子查詢都是相同的(好吧,在我的範例中,ORDER BY
子句中也使用的第二個列名是不同的,但在您的情況下它將始終是Revision
)。子查詢只能返回一個標量值,所以我FOR XML
用來將兩個必需的列打包成一個值,然後可以在外部解包SELECT
。USE [tempdb]; GO -- DROP FUNCTION dbo.MaxRevision; CREATE FUNCTION dbo.MaxRevision(@TableName sysname) RETURNS TABLE AS RETURN SELECT tab.BaseData.value(N'/row[1]/@object_id', N'BIGINT') AS [ObjectID], tab.BaseData.value(N'/row[1]/@some_id', N'BIGINT') AS [SomeID] FROM ( SELECT CASE @TableName WHEN N'objects' THEN (SELECT TOP (1) [object_id], [schema_id] AS [some_id] FROM master.sys.tables ORDER BY [schema_id] DESC FOR XML RAW, TYPE) WHEN N'indexes' THEN (SELECT TOP (1) [object_id], [index_id] AS [some_id] FROM master.sys.indexes ORDER BY [index_id] DESC FOR XML RAW, TYPE) WHEN N'columns' THEN (SELECT TOP (1) [object_id], [column_id] AS [some_id] FROM master.sys.columns ORDER BY [column_id] DESC FOR XML RAW, TYPE) END AS [BaseData] ) tab; GO
測試:
SELECT * FROM dbo.MaxRevision(N'objects'); SELECT * FROM dbo.MaxRevision(N'indexes'); SELECT * FROM dbo.MaxRevision(N'columns'); SELECT * FROM dbo.MaxRevision(N'none'); -- 1 row of NULL, NULL
SQLCLR
SQLCLR 允許在 TVF 中使用動態 SQL,因此您可以傳入表名,將其連接到查詢中(在您驗證它實際上是一個表名之後!),差不多就是這樣。
與純 T-SQL 選項相比,此選項提供了更多的靈活性,但實現起來也有點複雜。堅持使用純 T-SQL 選項,除非您需要更大的靈活性,例如動態定義查詢、從臨時表中讀取等。
您必須傳遞表變數而不是表名才能從此函式中獲取結果。即使您想提供表名,也必須將其作為 NVARCHAR 數據類型傳遞。
解決您的問題的其他可能選項(解決方法)如下。
- 您可以修改您的功能,並可能在您想要使用該功能的地方修改查詢的語法/邏輯。您可以使用 IDNET_CURRENT 內置函式來獲取給定表的 Max Identity 值。
- 您還可以避免使用使用者定義的函式。例如
CREATE TABLE EntityTable2 ( [Id] [uniqueidentifier] NOT NULL ,[Revision] [bigint] IDENTITY(1, 1) NOT NULL ) INSERT INTO EntityTable2 VALUES(NEWID()) INSERT INTO EntityTable2 VALUES(NEWID()) INSERT INTO EntityTable2 VALUES(NEWID()) INSERT INTO EntityTable2 VALUES(NEWID()) INSERT INTO EntityTable2 VALUES(NEWID()) INSERT INTO EntityTable2 VALUES(NEWID()) GO SELECT * FROM dbo.EntityTable2 WHERE Revision = IDENT_CURRENT('dbo.EntityTable2')
這些是您可以用來解決此問題的幾個選項。