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 數據類型傳遞。

解決您的問題的其他可能選項（解決方法）如下。

1. 您可以修改您的功能，並可能在您想要使用該功能的地方修改查詢的語法/邏輯。您可以使用 IDNET_CURRENT 內置函式來獲取給定表的 Max Identity 值。
2. 您還可以避免使用使用者定義的函式。例如

       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')

這些是您可以用來解決此問題的幾個選項。

引用自：https://dba.stackexchange.com/questions/217235