Joining table UDFs in queries

Hi,
I've got a table UDF which takes two parameters and returns a table, as
follows:
CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
datetime)
RETURNS @.tblPeriodYear TABLE
(
Period tinyint,
Year smallint
)
AS
BEGIN
<snipped to save space>
RETURN
END
That works fine. However, is it possible to use this UDF as part of a query
where the input parameters come from another table?
E.g. the two input parameters I want to pass to the function are contained
within the Sales table, and I could output them as follows:
SELECT
CompanyID,
SaleDate,
<other fields>
FROM
Sales
Ideally, I'm looking for some way of combining the query on the table with
the UDF e.g.
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM
Sales
Is this even possible?
Any assistance gratefully received.
MarkI'm afraid not in SQL Server 2000. This is new functionality added in SQL
Server 2005 via the APPLY table operator, e.g.,
SELECT ...
FROM Sales AS S
CROSS APPLY ftblPeriodYear(S.CompanyID, S.SaleDate) AS F;
You can find more details here:
http://www.windowsitpro.com/Article...47145.html?Ad=1
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Mark Rae" <mark@.mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:eueZ2kxyFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've got a table UDF which takes two parameters and returns a table, as
> follows:
> CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
> datetime)
> RETURNS @.tblPeriodYear TABLE
> (
> Period tinyint,
> Year smallint
> )
> AS
> BEGIN
> <snipped to save space>
> RETURN
> END
> That works fine. However, is it possible to use this UDF as part of a
> query where the input parameters come from another table?
> E.g. the two input parameters I want to pass to the function are contained
> within the Sales table, and I could output them as follows:
> SELECT
> CompanyID,
> SaleDate,
> <other fields>
> FROM
> Sales
> Ideally, I'm looking for some way of combining the query on the table with
> the UDF e.g.
> SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
> FROM
> Sales
>
> Is this even possible?
> Any assistance gratefully received.
> Mark
>|||That :
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM=20
Sales=20
doesn=B4t work. :-(|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1128675411.933739.210250@.g14g2000cwa.googlegroups.com...

>That :
>SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
>FROM
> Sales
>
>doesnt work. :-(
Er, yeah I know - that was the reason for my post...|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:unelksxyFHA.3720@.TK2MSFTNGP14.phx.gbl...

> I'm afraid not in SQL Server 2000. This is new functionality added in SQL
> Server 2005 via the APPLY table operator, e.g.,
Thanks - I was vaguely aware that there was something like this in SQL
Server 2005, but wondered if it had an equivalent in 2000...