CODEWITHSUNDEEP
sqlsql-serversql-server-2012
007
007

Reputation: 2186

TSQL - The multi-part identifier bounding error

CREATE FUNCTION [dbo].[Test] (@ID INT, @VAL INT)
RETURNS @Return TABLE (ID INT, VAL INT)
AS
BEGIN
    INSERT INTO @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 INTO @T1
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL 
SELECT 4

INSERT INTO @T2
SELECT 1,1
UNION
SELECT 2,4
UNION
SELECT 3,3

SELECT  *
FROM    @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 into the 2nd param of the fx if available, else pass in T1.Val. Changing the FX definition is not possible.

I can't seem to get this work. I tried ISNULL and that doesn't work either.

Upvotes: 1

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you want to call a table valued function, use APPLY (OUTER APPLY in this case because you are using LEFT JOIN):

SELECT  *
FROM @T1 T1 LEFT JOIN
     @T2 T2 
     ON T1.[ID] = T2.[ID] OUTER APPLY
     [dbo].[Test](1, COALESCE(T2.[VAL], T1.VAL) ) T;

If you want an additional condition, then use a WHERE clause:

SELECT  *
FROM @T1 T1 LEFT JOIN
     @T2 T2 
     ON T1.[ID] = T2.[ID] OUTER APPLY
     [dbo].[Test](1, COALESCE(T2.[VAL], T1.VAL) ) T
WHERE t1.ID = T.ID;

That last condition seems strange, though. Why not just pass T1.ID into the function directly?

Upvotes: 3

Related Questions