Odd behavior when calling a user function within an aggregation function

While debugging a code on the sql server 2000 debugger, I have encountered a
strange behavior while calling a user function from whitin the sum
aggregation function.
I have reproduced this behavior with the following code:
CREATE FUNCTION Test1 (@.prm decimal(18,2))
RETURNS decimal(18,2)
AS
BEGIN
return @.prm
END
---
CREATE PROCEDURE Test2
AS
declare @.var decimal(18,2)
set @.var=0
select @.var=sum(dbo.test1(Col1))
from Tbl1
print @.var
---
Col1 is a decimal(18,2) column on table Tbl1.
Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
for each row of Tbl1, but the sum result is ok.
For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
the result will be 3 as it should be.
On my original code I am using a recursive function that is running on a
tree and not a simple one like in this example, so in my case it harms the
performance quite a lot (actually any node on the tree will be executed 2^h
when h is the height of the node).
I replaced the sum function with the variable that sums itself the values as
followed:
select @.var = @.var + dbo.test1(Col1)
But still i'm not sure if there's no a better idea to solve it.
What is the cause of this behavior? Is it only while debugging?
Any ideas and thoughts will be appreciated.
Thanks, apscalar function gets invoked once per row. this is by design, i'm afraid.
-oj
<ap> wrote in message news:%23blucZYRFHA.2736@.TK2MSFTNGP09.phx.gbl...
> While debugging a code on the sql server 2000 debugger, I have encountered
> a strange behavior while calling a user function from whitin the sum
> aggregation function.
> I have reproduced this behavior with the following code:
> CREATE FUNCTION Test1 (@.prm decimal(18,2))
> RETURNS decimal(18,2)
> AS
> BEGIN
> return @.prm
> END
> ---
> CREATE PROCEDURE Test2
> AS
> declare @.var decimal(18,2)
> set @.var=0
> select @.var=sum(dbo.test1(Col1))
> from Tbl1
> print @.var
> ---
> Col1 is a decimal(18,2) column on table Tbl1.
> Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
> for each row of Tbl1, but the sum result is ok.
> For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
> enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
> the result will be 3 as it should be.
> On my original code I am using a recursive function that is running on a
> tree and not a simple one like in this example, so in my case it harms the
> performance quite a lot (actually any node on the tree will be executed
> 2^h when h is the height of the node).
> I replaced the sum function with the variable that sums itself the values
> as followed:
> select @.var = @.var + dbo.test1(Col1)
> But still i'm not sure if there's no a better idea to solve it.
> What is the cause of this behavior? Is it only while debugging?
> Any ideas and thoughts will be appreciated.
> Thanks, ap
>
>|||>> What is the cause of this behavior?
In the topic
http://msdn.microsoft.com/library/e...des_08_460j.asp
they say:
"The number of times that a function specified in a query is actually
executed can vary between execution plans built by the optimizer."
This behaviour can also be revealed by using the "Show Server Trace"
option in Query Analyzer or using Profiler, so it seems that it does
not happen only while debugging.
You can use this statement, instead:
SELECT @.var=SUM(X) FROM (
select top 100 percent dbo.test1(Col1) X
from Tbl1 order by X
) A
It seems that this way the function is called only once for each row.
Razvan

ODBC Sql Server Query Error

Hi,

I'm attempting to use the SQLExecute function to make a query to a SQL Server DB through ODBC.

However, the column I am trying to retrieve is called "date". When I try and query, I get back the message, "S0022: Invalid column name 'date'".

I believe I'm getting the error because date is a sql keyword (however if I run the query directly through SQL Interactive, I do not get a problem).

If anyone knows how I can get around this problem, that would be very helpful, as it is not possible to change the column name.

Thanks in advance.
Khalid.I'd use brackets around the offending columns. Something like:SELECT [date] FROM naughtyTable-PatP|||Gave that a try -- gives the same error. Any other ideas?

Thanks,
Khalid.|||try it with doublequotes

select "date" from naughtyTable|||you could also try functionizing it
select dateadd(d,0,[date]) as mydate from naughtyTable|||That will just return "date" for every row.

Post your query.

I have a sneaky suspicion something else is going on here like maybe the column does not exist or there is some imprperly formatted inline SQL.

Otherwise what Pat said should have worked.|||That will just return "date" for every row.wrong

you may try it for yourself --create table datetest
( id tinyint not null primary key
, date datetime
)

insert into datetest (id, date) values ( 1, getdate() )
insert into datetest (id, [date]) values ( 2, '11/11/2005' )
insert into datetest (id, "date") values ( 3, '2005-01-19' )

select id, "date" from datetestwant to know what this produces?

1 2005-01-18 15:32:29.163
2 2005-11-11 00:00:00.000
3 2005-01-19 00:00:00.000|||Alright you win this round Batman.

I was thinking about single qoutes which would spit out Date for every row. I have to stop posting at this during the day. I start getting a little flitty.

ODBC Link from Access to SQL Server 2005 Stored Function

If I define a table-valued function in a SQL Server 2005 database, can I link to it from Access 2003 using ODBC?

I've defined the function successfully, and I can link from Access to tables in the database (so my ODBC link is basically functioning), but I can't see the table-valued function in the Linked Table Manager in Access.

I can define a pass-through query to grab the table, but with a pass-through query I have to provide the ODBC password every time.

What am I missing?

Suggestions?

Try creating a view around your table valued function, and then linking to the view. It worked for the sample I just tried.

Thanks