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.MyScalarFunction() )

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.MyScalarFunction()
...
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]

"Yarik" <ya***@garlic.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

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.MyScalarFunction() )

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.MyScalarFunction()
...
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.

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]

Yarik (ya***@garlic.com) writes:

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.MyScalarFunction() )

SQL Server issues the following complaint:

Incorrect syntax near '.'.

I am really perplexed: what I am doing wrong?
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.
Interestingly, if I re-write the second snippet as

DECLARE @i AS int
SELECT @i = dbo.MyScalarFunction()
...
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...

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp