Sql-server – SQL Server: Pass table name into table valued function as a parameter

set-returning-functionssql servert-sqltable-valued-parameters

All of my tables have those two columns:

[Id] [uniqueidentifier] NOT NULL
[Revision] [bigint] IDENTITY(1, 1) NOT NULL

Every other time I write a query, I want to eliminate some of the rows. The elimination conditions are the same for every table. I want an easy way to specify those conditions once and then reuse them on any table.

I defined a table type as a "base class" for my tables

CREATE TYPE EntityTable AS TABLE (
    [Id] [uniqueidentifier] NOT NULL
    ,[Revision] [bigint] IDENTITY(1, 1) NOT NULL
)

And here's a table valued function I create

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
        )

I thought I would be able to use it like that

SELECT * FROM MaxRevision(NameOfSomeTableWhichHasIdAndRevisionColumn) WHERE /* some other conditions */

But I'm getting the error

Msg 207, Level 16, State 1, Line 2
Invalid column name 'NameOfSomeTableWhichHasIdAndRevisionColumn'.

Why it treats the table name I pass as a column name? Are there any other ways to implement the behavior I want?

Best Answer

Table-Valued Parameters (TVPs), and T-SQL in general, does not work like that. There is no way to define a partial schema as an interface and then pass in table that matches that interface. For TVPs, you need to pass in a table variable created from that table type. So clearly not the way to go here.

Also, you have said that all of these tables have those two columns, but not that all of these tables have all of the same columns. This is an important distinction because table-valued functions are required to return a consistent result set; the result set cannot be dynamic like it can be with stored procedures. I am mentioning this because your example function does a SELECT * from the TVP, implying that you might be expecting this function to return whatever columns are in whatever table name you pass in. For this function, you will need to return only the two columns that all of the tables have in common: Id and Revision.

That being said, there are two ways you can accomplish this: pure T-SQL and SQLCLR.

PURE T-SQL

The function uses a CASE statement to determine which sub-query to execute. Each sub-query is the same except for the table name (well, in my example the second column name, which is also used in the ORDER BY clause, is different, but in your situation it will always be Revision). Sub-queries can only return a scalar value, so I am using FOR XML to package up the two required columns into one value that can then be unpacked in the outer 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

The tests:

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 allows for Dynamic SQL in a TVF, so you can pass in the table name, concatenate it into the query (after you verify that it is, in fact, a table name!), and that's pretty much it.

This option allows for a little more flexibility than the pure T-SQL option, but is also a bit more complicated to implement. Stick with the pure T-SQL option unless you need more flexibility, such as dynamically defining the queries, reading from temporary tables, etc.

Related Question