Problem with nested function call (UDFs)

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.

This doesn't seem to work at all. Regardless whether I provide the
namespace (schema) calling the scalar UDF or not. Error message is
just different.

Both functions operate correctly invoked un-nested.

The whole expression does work fine if I turn 'get_trxns_for_quarter'
into a scalar UDF as well, e.g. by returning just one trxn_gu_id with
e.g. MAX() in a scalar datatype. But of course that's no good to me.

It also works fine if I select the result of 'get_current_quarter'
into a variable and pass that variable into 'get_trxns_for_quarter'.
But that's no good to me either since then I cannot use the whole
thing embedded into other SELECT clauses.

Both UDF's are non-deterministic but I couldnt see how that would have
an impact anyway.

Never mind the syntax on that example or anyhting, I tried all the
obvious and not so obvious stuff and it really seems to come down to
the fact that one UDF is scalar and the other one is not. However, I
did not come across any type of information saying that this cannot be
done.

Have you any ideas?

Any help would be greatly appreciated.

Carsten

Re: Problem with nested function call (UDFs)

You are right, you can't do that.

BOL doesn't explicitely say that it is impossible (at least I didn't
find it), it only mentions that no columns from any outer query can be
used as parameter. I think it is not possible because scalar UDFs are
executed for each (relevant) row, which means that by definition they
cannot be used as a parameter to a table valued UDF since it needs a
scalar per parameter.

The obvious solution would be to create a new table valued UDF that
implements both the current table valued and current scalar UDF (and
takes the parameter of the scalar UDF).

If you do not want duplication of code, then you could consider building
a table "current_quarters" and fill it with values returned by the
scalar UDF, and join to that table whenever you need to determine the
current_quarter.

Having said all that, from a performance point of view, if you want only
one value for a query, then you want to calculate it before hand. You
don't want to put a scalar UDF in the query if this UDF will return the
same value over and over again. Assigning a value to a local variable,
or implementing the logic in a table valued UDF will do just that.

HTH,
Gert-Jan


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.
>
> This doesn't seem to work at all. Regardless whether I provide the
> namespace (schema) calling the scalar UDF or not. Error message is
> just different.
>
> Both functions operate correctly invoked un-nested.
>
> The whole expression does work fine if I turn 'get_trxns_for_quarter'
> into a scalar UDF as well, e.g. by returning just one trxn_gu_id with
> e.g. MAX() in a scalar datatype. But of course that's no good to me.
>
> It also works fine if I select the result of 'get_current_quarter'
> into a variable and pass that variable into 'get_trxns_for_quarter'.
> But that's no good to me either since then I cannot use the whole
> thing embedded into other SELECT clauses.
>
> Both UDF's are non-deterministic but I couldnt see how that would have
> an impact anyway.
>
> Never mind the syntax on that example or anyhting, I tried all the
> obvious and not so obvious stuff and it really seems to come down to
> the fact that one UDF is scalar and the other one is not. However, I
> did not come across any type of information saying that this cannot be
> done.
>
> Have you any ideas?
>
> Any help would be greatly appreciated.
>
> Carsten

Re: Problem with nested function call (UDFs)

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

Re: Problem with nested function call (UDFs)

Carsten (carsten.martens@gmail.com) writes:
> '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.

Be very careful with doing data access from scalar UDFs. It can have
adverse effect on performance.

I think Gert-Jan said this in his post, but I just wanted to
emphasize it more strongly.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Problem with nested function call (UDFs)

Hugo Kornelis wrote:
>
> 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.

Hugo, you are right (as usual). The trick is in schemabinding the scalar
UDF (and addressing the UDF with its 2-part name).

Do you have any documentation or BOL reference that specifies that
schemabinding is required? Because I don't understand why this would be
relevant.

Thanks,
Gert-Jan

Re: Problem with nested function call (UDFs)

On Sat, 21 Jul 2007 00:14:51 +0200, Gert-Jan Strik wrote:

>Hugo Kornelis wrote:
>>
>> 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.
>
>Hugo, you are right (as usual). The trick is in schemabinding the scalar
>UDF (and addressing the UDF with its 2-part name).
>
>Do you have any documentation or BOL reference that specifies that
>schemabinding is required? Because I don't understand why this would be
>relevant.

Hi Gert-Jan,

I have no such reference, and there is none - because schemabinding is
not required. Here's a repro:

DROP FUNCTION dbo.get_current_quarter;
go
CREATE FUNCTION dbo.get_current_quarter (@dt datetime)
RETURNS datetime
AS
BEGIN;
DECLARE @BegQ datetime;
SET @BegQ = DATEADD(quarter, DATEDIFF(quarter, 0, @dt), 0);
RETURN (@BegQ);
END;
go
DROP FUNCTION dbo.get_trxns_for_quarter;
go
CREATE FUNCTION dbo.get_trxns_for_quarter (@dt datetime)
RETURNS TABLE
AS
RETURN (SELECT DATEADD(month, Number - 1, @dt) AS TheDate
FROM dbo.Numbers
WHERE Number BETWEEN 1 AND 3);
go
SELECT *
FROM dbo.get_trxns_for_quarter(dbo.get_current_quarter(GetDate()) );
go
SELECT id, dt
FROM (SELECT 1, CAST('20070701' AS datetime)
UNION ALL
SELECT 2, CAST('20070815' AS datetime)) AS t(id, dt)
WHERE dt IN (SELECT TheDate
FROM
dbo.get_trxns_for_quarter(dbo.get_current_quarter(GetDate()) ));
go


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

Re: Problem with nested function call (UDFs)

Sorry about the confusion. I first tested on SQL Server 2000 and later
(when Hugo gave a reply) I retested on 2005.

In SQL Server 2005, this all works just fine. All you need to do is
access the scalar UDF with its 2-part name.

However, in SQL Server 2000 (the OP's platform), it doesn't work.
Calling it with the 2-part name gives the error "Incorrect syntax near
'.'", calling it with the 1-part name gives the error "Incorrect syntax
near '('.". Schemabinding doesn't make a difference.

Gert-Jan

Re: Problem with nested function call (UDFs)

On Sun, 22 Jul 2007 00:42:01 +0200, Gert-Jan Strik wrote:

>Sorry about the confusion. I first tested on SQL Server 2000 and later
>(when Hugo gave a reply) I retested on 2005.
>
>In SQL Server 2005, this all works just fine. All you need to do is
>access the scalar UDF with its 2-part name.
>
>However, in SQL Server 2000 (the OP's platform), it doesn't work.
>Calling it with the 2-part name gives the error "Incorrect syntax near
>'.'", calling it with the 1-part name gives the error "Incorrect syntax
>near '('.". Schemabinding doesn't make a difference.

Hi Gert-Jan,

I missed both the requirement of the OP that it run under SQL2000 and
the odd behaviour on that platform.

I can confirm the same behaviour here. And I can't find any explanation
in Books Online. I guess it's a bug - but not one I expect to ever see
fixed.

The workaround is of course simple:

DECLARE @d datetime
SET @d = dbo.get_current_quarter(GetDate())
SELECT id, dt
FROM (SELECT 1, CAST('20070701' AS datetime)
UNION ALL
SELECT 2, CAST('20070815' AS datetime)) AS t(id, dt)
WHERE dt IN (SELECT TheDate
FROM dbo.get_trxns_for_quarter(@d));


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

Re: Problem with nested function call (UDFs)

Thanks a lot guys,

I followed your notes with great interest.
What I will take from this is that you people, who (probably) are more
experienced than me on the SQL server technology, confirmed this
behaviour and agree with me that is not documented.

In order to embed these nested calls most elegantly into my queries I
really needed it the way I had it planned but I'm sure I will find a
suitable workaround. I'll keep the remarks on cost in mind, thank you
very much.

The thing is that these and other similar subqueries occur quite often
in my code base. My code base consist of hundreds of procs, some of
them used by some application modules, some of them used by reporting
modules, some of them probably not used at all...;-) Also this
codebase is a good few years old and had many different people working
it. So between all these hundreds of procs these subqueries are often
implemented differently (just a bit) which not only accounts for
undesired non-matching results (between different modules of the
application/reports) but this also makes the codebase difficult to
maintain. So my idea is to put these reoccuring blocks of inner joins
(thats what they are in my real world) into functions to make sure
that all the procs, all the applications logic retrieves - say-
transactions for a given quarter in the very same way. As I said there
is similar stuff on other tables and joins in my code and I was hoping
that once I found a suitbale way of replacing such stuff I could
consolidate the codebase entirely.

This has been very helpful nonetheless and I'm sure I'll find a way
around this. Thank you.

Carsten

Re: Problem with nested function call (UDFs)

Carsten wrote: [snip]
> I was hoping
> that once I found a suitbale way of replacing such stuff I could
> consolidate the codebase entirely.

Software maintenance can be very expensive. You could consider upgrading
to SQL Server 2005...

> This has been very helpful nonetheless and I'm sure I'll find a way
> around this. Thank you.

You're welcome.

Gert-Jan