Joining table UDFs in queries

7 Oct 2005 8:29 AM

Mark Rae

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

7 Oct 2005 8:43 AM

Itzik Ben-Gan

I'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/ArticleID/47145/47145.html?Ad=1

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp

--
BG, SQL Server MVP
www.SolidQualityLearning.com

Join us for the SQL Server 2005 launch at the SQL Week in Israel!
http://www.microsoft.com/israel/sql/sqlweek/default.mspx

Show quoteHide quote

"Mark Rae" <m***@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
>

7 Oct 2005 9:28 AM

Mark Rae

"Itzik Ben-Gan" <it***@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...

7 Oct 2005 8:56 AM

Jens

That :

SELECT
    CompanyID,
    SaleDate,
    ftblPeriodYear(CompanyID, SaleDate)
FROM
    Sales

doesn´t work. :-(

7 Oct 2005 9:26 AM

Mark Rae

Show quote Hide quote

"Jens" <J***@sqlserver2005.de> wrote in message
news:1128675411.933739.210250@g14g2000cwa.googlegroups.com...

>That :
>
>SELECT
>    CompanyID,
>    SaleDate,
>    ftblPeriodYear(CompanyID, SaleDate)
>FROM
>    Sales
>
>
>doesn´t work. :-(

Er, yeah I know - that was the reason for my post...

Thread options