The problem here is that you are mixing a table and a scalar value.

First you create your iTVF GetBench. It receives a varchar(20) as the only parameter. However, in your query you are mixing the references.

I would recommend making this a bit simpler until you get the hard parts ironed out. Specifically, don't do an insert until you get the select working. Drop all the grouping and just pull select *.

Let's start here:

DECLARE @benches TABLE (Bench varchar(20));

INSERT @benches

values

('LabC1'),

('LabC2'),

('LabC3'),

('LabC4');

SELECT *

FROM @benches

So far so good. You have a table variable, it is populated with some rows and you can select them.

So now you want to get some additional results by joining to your iTVF. However, in the code you posted you are passing it a table variable, this is NOT a varchar(20) as defined by your function.

I think that you want to pass a single row's value here but I am not entirely sure.

That would be something like this.

SELECT *

FROM @benches b

OUTER APPLY dbo.GetBench(b.Bench)

Does that help?

From your earlier post you could change your function to use schema binding by simply using 2 part naming instead of 3...

CREATE FUNCTION GetBench

(

@benches varchar(20)

)

RETURNS TABLE WITH SCHEMABINDING

AS RETURN

SELECT r.createdate

FROM dbo.Runs r

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

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

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

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