Impossible to use a scalar UDF to pass a parameter to another UDF?

[Yarik]

Hello there!

I am working with MS SQL Server 2000.

I have a table function that takes an integer parameter and returns a
table, and I can successfully use it like this (passing a literal
as a parameter):

SELECT * FROM MyTableFunction (1)

or like this (passing a variable as a parameter):

DECLARE @i AS int
SELECT @i = 10
...
SELECT * FROM MyTableFunction (@i)

Now, if I have another function -- a scalar function that returns an
integer, I cannot find a way to use it to specify the parameter value
for the first function. For example, when I write

SELECT * FROM MyTableFunction ( dbo.MyScalarFun ction() )

SQL Server issues the following complaint:

Incorrect syntax near '.'.

I am really perplexed: what I am doing wrong?

Interestingly, if I re-write the second snippet as

DECLARE @i AS int
SELECT @i = dbo.MyScalarFun ction()
...
SELECT * FROM MyTableFunction (@i)

everything works just fine; however, this trick cannot be used as a
workaround because I need to pass result of one function as a parameter
to another inside a view's code -- I cannot declare variables and write
any procedural code...

Any ideas, especially workarounds, would be greatly appreciated.

Thank you,
Yarik.

[Simon Hayes]

Re: Impossible to use a scalar UDF to pass a parameter to another UDF?


"Yarik" <yarik@garlic.c om> wrote in message
news:1108688359 .181165.33220@c 13g2000cwb.goog legroups.com...[color=blue]
> Hello there!
>
> I am working with MS SQL Server 2000.
>
> I have a table function that takes an integer parameter and returns a
> table, and I can successfully use it like this (passing a literal
> as a parameter):
>
> SELECT * FROM MyTableFunction (1)
>
> or like this (passing a variable as a parameter):
>
> DECLARE @i AS int
> SELECT @i = 10
> ...
> SELECT * FROM MyTableFunction (@i)
>
> Now, if I have another function -- a scalar function that returns an
> integer, I cannot find a way to use it to specify the parameter value
> for the first function. For example, when I write
>
> SELECT * FROM MyTableFunction ( dbo.MyScalarFun ction() )
>
> SQL Server issues the following complaint:
>
> Incorrect syntax near '.'.
>
> I am really perplexed: what I am doing wrong?
>
> Interestingly, if I re-write the second snippet as
>
> DECLARE @i AS int
> SELECT @i = dbo.MyScalarFun ction()
> ...
> SELECT * FROM MyTableFunction (@i)
>
> everything works just fine; however, this trick cannot be used as a
> workaround because I need to pass result of one function as a parameter
> to another inside a view's code -- I cannot declare variables and write
> any procedural code...
>
> Any ideas, especially workarounds, would be greatly appreciated.
>
> Thank you,
> Yarik.
>[/color]

It seems that you can't do what you want directly - I guess it's a
limitation/feature of how UDFs were implemented. The most obvious
workarounds (to me) would be to rewrite your view as a function or stored
procedure, if that's possible - you might want to post your function code if
it's not tool complex, to see if someone can suggest an alternative way of
implmenting it.

Simon

[Erland Sommarskog]

Re: Impossible to use a scalar UDF to pass a parameter to another UDF?

Yarik (yarik@garlic.c om) writes:[color=blue]
> I have a table function that takes an integer parameter and returns a
> table, and I can successfully use it like this (passing a literal
> as a parameter):
>
> SELECT * FROM MyTableFunction (1)
>
> or like this (passing a variable as a parameter):
>
> DECLARE @i AS int
> SELECT @i = 10
> ...
> SELECT * FROM MyTableFunction (@i)
>
> Now, if I have another function -- a scalar function that returns an
> integer, I cannot find a way to use it to specify the parameter value
> for the first function. For example, when I write
>
> SELECT * FROM MyTableFunction ( dbo.MyScalarFun ction() )
>
> SQL Server issues the following complaint:
>
> Incorrect syntax near '.'.
>
> I am really perplexed: what I am doing wrong?[/color]

Not reading the manual and getting updated on the rules for calling
table functions, I guess. I'm too lazy to do it myself, but you
can probably not pass expression - that's the same as for stored
procedures.
[color=blue]
> Interestingly, if I re-write the second snippet as
>
> DECLARE @i AS int
> SELECT @i = dbo.MyScalarFun ction()
> ...
> SELECT * FROM MyTableFunction (@i)
>
> everything works just fine; however, this trick cannot be used as a
> workaround because I need to pass result of one function as a parameter
> to another inside a view's code -- I cannot declare variables and write
> any procedural code...[/color]

Not sure I get this, but I don't see the difference. Keep in mind that
you cannot pass a table column as a parameter to a table-valued function.
Think of it for a while and you realize that for each colunm you get a
new table.

Yes, yes, it could still be useful and in SQL 2005 there is actually
a new join operator to permit this. But in SQL2000 you can't.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp