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. 2 2424
"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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: roger |
last post by:
I'm having difficulties invoking a user defined table function,
when passing to it a parameter that is the result of another
user defined function.
My functions are defined like so:
drop...
|
by: Martin MacRobert |
last post by:
Hi,
I'm trying to make a specialisation of a template function, so that
the second parameter accepts scalar types only (int,double,float
etc.).
How can I do this without writing an explicit...
|
by: Ken Adeniji |
last post by:
Must declare the scalar variable '@FirstName'
ContactGridViewWebForm.aspx
<aspqlDataSource
RunAt="server"
ID="SqlDataSourceContact"
...
|
by: deepdata |
last post by:
Hi,
I am trying to create a function which returns a scalar data. My
function tries to insert a row in a table as well.
/**
CREATE function getManufacturerId
(
manufacturer VARCHAR(128)
)
|
by: David++ |
last post by:
Hi folks,
I would be interested to hear peoples views on whether or not 'pass by
reference' is allowed when using a Web Service method.
The thing that troubles me about pass-by-reference into...
|
by: Rama Jayapal |
last post by:
the following is my code
can anybody rectify my problem that i get when running my application
"Must declare scalar variable @ID"
<asp:GridView ID="GridView1" DataKeyNames="ID" runat="server"...
|
by: roamnet |
last post by:
hi
i created database file with .mdf extention ,sql server as a source
and use grid view to display data
there're no problem in data retrieve and display,but i want to edit it
or insert new...
|
by: raylopez99 |
last post by:
Keywords: scope resolution, passing classes between parent and child
forms, parameter constructor method, normal constructor, default
constructor, forward reference, sharing classes between forms....
|
by: Tony M |
last post by:
vs 2005 - vb .net - web forms - xp pro
Can't figure out why I keep getting the error Must declare the scalar
variable "@CheckUser".
The error happens on SqlDA.Fill(SqlDS). Just trying to make...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |