Stored procedure joining a table function?

Question

I have a stored procedure that needs to do an inner join on a function that returns a table. Trying to call it like so:

INNER JOIN [dbo].[udf_GetBillingInfo(@QuoteID)] billInfo ON quotes.QuoteID = billInfo.QuoteID

gives an error invalid object name dbo.udf_GetBillingInfo(@QuoteID)

Given my requirements I don't think I can just make the function into a view as it has a minor bit of logic in it. How can I join the result? Would I have to assign the function result to a variable or something of that nature?

you should be able to join to a TVF. you sure schema and tvf name is correct? — Mitch Wheat, Aug 11 '11 at 14:48

score 5 · Accepted Answer · answered Aug 11 '11 at 14:52

5

Your problem is the square brackets - [dbo].[udf_GetBillingInfo(@QuoteID)] - they quote whatever is inside as the function name.

Change it to this:

[dbo].udf_GetBillingInfo(@QuoteID)

answered Aug 11 '11 at 14:52

cjk

45,739
9
81
112

Hah, that was it; I forgot that [] denotes a **column** name, so it was thinking the name of the function *and* the parameter was a column. Failz. – Wayne Molina Aug 11 '11 at 14:57
or [dbo].[udf_GetBillingInfo](@QuoteID) – canon Aug 11 '11 at 15:00
D'oh! I can't believe I missed that one! +1 – Mitch Wheat Aug 11 '11 at 15:06

score 0 · Answer 2 · answered Aug 11 '11 at 14:53

Here is an (dumb) example of joining to a TVF, hope it helps!

CREATE FUNCTION MyFunc ( @p1 int ) RETURNS TABLE 
AS
RETURN 
(
        SELECT @p1 AS a 
        union 
        SELECT @p1 +1 AS a 
        union 
        SELECT @p1 +2 AS a
)
GO

declare @t table (c int) 
insert @t (c) values(1),(2),(3),(4)

select * 
from @t t 
left join dbo.MyFunc(1) f on  t.c = f.a

score -1 · Answer 3 · edited May 23 '17 at 11:55

-1

Can you write you function as a table function? I think you may find the answer here

tsql returning a table from a function or store procedure

edited May 23 '17 at 11:55

Community

1
1

answered Aug 11 '11 at 14:47

Keith Bloom

2,391
3
18
30

Indeed. Having re-read the question I can see you actually said it was a table function. – Keith Bloom Aug 11 '11 at 21:00

Stored procedure joining a table function?

3 Answers3