Multi Statement Table Valued Functions In A Join

Question

Post reply

Multi Statement Table Valued Functions In A Join

Jamie Batiste

SSCommitted

Points: 1880
More actions
March 14, 2008 at 5:42 am

#71716

Hi
Normally we use either sub queries or scalar functions (very occassionally) to perform updates, however I have a case where a multi statement table valued function is necessary but I can't seem to call it in a join.
E.g.
CREATE FUNCTION [dbo].[fn_Find_Security_Details] ( @InvestmentCode Varchar(12), @DateRequired as Datetime) RETURNS @tmpSecurity_Detail TABLE (InvestmentCode varchar(12), Bid Numeric(28,9), Price_Date DateTime)
AS
BEGIN
--....
--Calculates the best price based on date returns single line of data.
INSERT INTO @tmpSecurity_Detail
SELECT @InvestmentCode, @Bid , @History_Date
RETURN
END
GO
--To use as a table this works...
select * from fn_Find_Security_Details('3384530', '2007-07-31')
All is good and we cut down on the number of scalar functions to determine both bid and date, except that I can't then use the function in a join
create table #tmpused (sedol_number varchar(12) , asat datetime, price numeric(19,6) null)
go
insert into #tmpUsed (sedol_number, asat)
values ( 3384530, '2007-07-31' )
go
Update #TmpUsed
Set Price = f.Bid, Date= f.Price_date
from #tmpUsed t inner join dbo.fn_Find_Security_Details(t.Sedol_Number, t.AsAt) f on f.sedol_number=t.sedol_number
I don't want to remove the date and sedol_number as a parameter to the function as this means the result set would contain every stock and day it was priced which would be horrific!
Am I missing something?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Jamie

Will this work?

...FROM #tmpUsed t INNER JOIN (

SELECT Bid, Price_date, sedol_number

FROM dbo.fn_Find_Security_Details(Sedol_Number, AsAt)

) f

ON f.sedol_number=t.sedol_number

John

Jamie Batiste SSCommitted Points: 1880 More actions · Answer 2

John Mitchell (3/14/2008)
Jamie
Will this work?
...FROM #tmpUsed t INNER JOIN (
SELECT Bid, Price_date, sedol_number
FROM dbo.fn_Find_Security_Details(Sedol_Number, AsAt)
) f
ON f.sedol_number=t.sedol_number
John

Hi John

Thanks for replying. Sadly it doesn't work, I am trying to link the sedol from the temporary table and I either get "Incorrect syntax near 'Sedol_Number'." or if I add the temporary table alias t in front e.g. "dbo.fn_Find_Security_Details(t.sedol_number, t.asat)" in the function parameter "Incorrect syntax near 't'".

Kind Regards

Jamie

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

OK, how about putting the result set of the function into a temp table:

SELECT dbo.fn_Find_Security_Details(Sedol_Number, AsAt)

INTO #MyTempTable

FROM #tmpUsed

and then joining to that instead?

John

Jamie Batiste SSCommitted Points: 1880 More actions · Answer 4

Thanks for your help John, I've decided to leave Table Valued Functions until they change somewhat as the limitations for what we want to achieve are too restrictive!

I'll stick to stored procedures and scalar functions for the moment.

Kind Regards

Jamie

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 5

This is a great example where SQL Server 2005 is better.

Then all you have do to is use the CROSS APPLY operator.

Updatet

Sett.Price = f.Bid,

t.Date= f.Price_date

from#tmpUsed as t

cross applydbo.fn_Find_Security_Details(t.Sedol_Number, t.AsAt) f

N 56°04'39.16"
E 12°55'05.25"