Problem with nested function call (UDFs)

[Carsten]

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

[Gert-Jan Strik]

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

[Hugo Kornelis]

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

[Erland Sommarskog]

Carsten (ca*************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Gert-Jan Strik]

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

[Hugo Kornelis]

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

[Gert-Jan Strik]

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