CODEWITHSUNDEEP
t-sqlstored-procedures
Wayne Molina
Wayne Molina

Reputation: 19596

Stored procedure joining a table function?

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?

Upvotes: 1

Views: 1344

Answers (3)

JohnD
JohnD

Reputation: 14767

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

Upvotes: 0

cjk
cjk

Reputation: 46465

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)

Upvotes: 5

Keith Bloom
Keith Bloom

Reputation: 2439

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

Upvotes: -1

Related Questions