polkadot (9/26/2013)

---

I'm trying to apply what I've learned in the APPLY links you left me, Luis, to my solution. Thus, I want a TVF for the right half of the query. By right half, I mean the query to the right of the OUTER APPLY which gets the createdate for each bench in the input field.

I'm getting error when I try to create the function:

CREATE FUNCTION GetBench (@bench varchar(20))

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

SELECT r.createdate

FROM Runs r

INNER JOIN runs_machines rm ON rm.runid = r.runid

INNER JOIN Machines m ON m.machineid = rm.machineid

WHERE m.NAME LIKE (@bench + '[0-9][0-9][0-9]')

AND r.createdate > dateadd(M, - 3, getdate())

error:

Msg 4512, Level 16, State 3, Procedure GetBench, Line 5

Cannot schema bind table valued function 'GetBench' because name 'Runs' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Can you point out what's wrong in the TVF? Thanks.

SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.