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:
That being said, there are two ways you can accomplish this: pure T-SQL and SQLCLR.
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
-- DROP FUNCTION dbo.MaxRevision;
CREATE FUNCTION dbo.MaxRevision(@TableName sysname)
RETURNS TABLE AS
SELECT tab.BaseData.value(N'/row/@object_id', N'BIGINT') AS [ObjectID],
tab.BaseData.value(N'/row/@some_id', N'BIGINT') AS [SomeID]
SELECT CASE @TableName
WHEN N'objects' THEN (SELECT TOP (1) [object_id], [schema_id] AS [some_id]
ORDER BY [schema_id] DESC FOR XML RAW, TYPE)
WHEN N'indexes' THEN (SELECT TOP (1) [object_id], [index_id] AS [some_id]
ORDER BY [index_id] DESC FOR XML RAW, TYPE)
WHEN N'columns' THEN (SELECT TOP (1) [object_id], [column_id] AS [some_id]
ORDER BY [column_id] DESC FOR XML RAW, TYPE)
END AS [BaseData]
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 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.