Posted by Hugo Kornelis on 07/20/07 20:26

On Fri, 20 Jul 2007 09:39:44 -0700, Carsten wrote:

>Hello Folks,
>
>I encountered a problem with SQL server 2000 and UDFs.
>
>I have a scalar UDF and a table UDF where I would like the scalar UDF
>to provide the argument for the table UDF like in:
>
>SELECT
> *
>FROM
> transaction_t
>WHERE
> trxn_gu_id in (
> select get_trxns_for_quarter(get_current_quarter( GetDate() ) )
> )
>
>'get_current_quarter' returns an integer which is a GUID in a table
>containing business quarter definitions, like start date, end date.
>'get_current_quarter' is a scalar UDF.
>'get_trxns_for_quarter' will then get all transctions that fall into
>that quarter and return their GUID's in a table.
>'get_trxns_for_quarter' is a table UDF.

Hi Carsten,

You need to select from a table-valued function. And you need to
schema-qualify UDF's.

SELECT Column1, Column2, ... -- Don't use SELECT * !!
FROM transaction_t
WHERE trxn_gu_id IN
(SELECT Column_name
FROM dbo.get_trxns_for_quarter
(dbo.get_current_quarter(CURRENT_TIMESTAMP)));

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]