|
|
Posted by Gert-Jan Strik on 07/20/07 20:07
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
Navigation:
[Reply to this message]
|