sql - TSQL - The multi-part identifier bounding error -

create function [dbo].[test] (@id int, @val int) returns @return table (id int, val int) begin     insert @return     select @id, @val return; end go declare @t1 table (id int identity(1,1), val int) declare @t2 table (id int, val int)  insert @t1 select 1 union select 2 union select 3 union  select 4  insert @t2 select 1,1 union select 2,4 union select 3,3  select  *    @t1 t1 left join @t2 t2 on t1.[id] = t2.[id] left join [dbo].[test] (1, coalesce(t2.[val],t1.val)) t on t1.id = t.id go  drop function [dbo].[test] go 

goal:

to pass in t2.val 2nd param of fx if available, else pass in t1.val. changing fx definition not possible.

i can't seem work. tried isnull , doesn't work either.

if want call table valued function, use apply (outer apply in case because using left join):

select  * @t1 t1 left join      @t2 t2       on t1.[id] = t2.[id] outer apply      [dbo].[test](1, coalesce(t2.[val], t1.val) ) t; 

if want additional condition, use where clause:

select  * @t1 t1 left join      @t2 t2       on t1.[id] = t2.[id] outer apply      [dbo].[test](1, coalesce(t2.[val], t1.val) ) t t1.id = t.id; 

that last condition seems strange, though. why not pass t1.id function directly?