By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,375 Members | 1,426 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,375 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"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
Jul 23 '05 #2

P: n/a
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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.