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. 2 2443
"Yarik" <ya***@garlic.c om> wrote in message
news:11******** *************@c 13g2000cwb.goog legroups.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.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.
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.c om) 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.MyScalarFun ction() )
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.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...
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****@sommarsk og.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 function dbo.scalar_func
go
create function dbo.scalar_func()
returns int
|
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 specialisation for all
scalar types? This is because of the large number of functions to
overload.
For example:
|
by: Ken Adeniji |
last post by:
Must declare the scalar variable '@FirstName'
ContactGridViewWebForm.aspx
<aspqlDataSource
RunAt="server"
ID="SqlDataSourceContact"
ConnectionString="server=localhost;database=WordEngineering;Integrated
Security=SSPI;"
ProviderName="System.Data.OleDb"
|
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 a WebService is that
essentially we are passing an address of an object which resides on the
'local machine' i.e. a local machine object address. Surely when the
WebService method is called and run 'on the server', the reference type will
be...
| |
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" AutoGenerateColumns="False" BackImageUrl="~/App_Themes/SkinFile/back1.jpg"
BorderColor="Teal" BorderStyle="Solid" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField...
|
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 records
there is an error "Incorrect syntax near '-'.
Must declare the scalar variable "@UserName".
I worked out in design view,code is automatically generated.Iam not
able fix the error.
Iam working with Visual Web Developer-2005 Express Edition
|
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.
Here is a newbie mistake that I found myself doing (as a newbie), and
that even a master programmer, the guru of this forum, Jon Skeet,
missed! (He knows this I'm sure, but just didn't think this was my
problem; LOL, I am needling him)
If...
|
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 sure EMail
address doesn't exist in database before I insert data.
Connection string and everything else is correct. If I use Dim SQL = "Select
* from Clients where EMail = " & chr(39) & "SomeEmailAddress@msn.com" &
chr(39) it works fine.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |