Skip to main content
edited tags
Link
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300
Bumped by Community user
Source Link
vorou
  • 193
  • 1
  • 4

SQL Server: Pass table name into table valued function as a parameter

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?