Bug using UDF's in Query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Bug using UDF's in Query?

Author

Topic

Billpl
Yak Posting Veteran

71 Posts

Posted - 2002-08-29 : 17:50:08

I don't know if this is a bug or not, but I getting some pretty funky results.
The following, I am using a UDF to create a conditional Sum.

----------
Select PortID,
Sum( dbo.App_SumAmount( OpenShares, 'SL', PosType ) ) as Long,
Sum( dbo.App_SumAmount( OpenShares, 'SS', PosType ) ) as Short
From App_position
Where Portid = 1017
Group by PortId
----------

Even though I pass different parameters to the function both Long and Short return the summation of the Long or which ever function is first in the field list.

----------
CREATE FUNCTION dbo.App_SumAmount (@Amount as decimal(10,1),
@PosType as char(3),
@MatchType as char(3) )

RETURNS Decimal(10,1)
AS
BEGIN
Declare @Result as Decimal(10,1)

If @PosType = @MatchType
Set @Result = @Amount
Else
Set @Result = 0

Return @Result
END
----------

Now if I replicate the UDF using a different name, it will work just fine. I'm wondering what the scope of this problem is or maybe there is some other reason for it.

----------
Select PortID,
Sum( dbo.App_SumAmount( OpenShares, 'SL', PosType ) ) as Long,
Sum( dbo.App_SumAmount2( OpenShares, 'SS', PosType ) ) as Short
From App_position
Where Portid = 1017
Group by PortId
----------

Thanks, Bill

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-30 : 03:56:15

It definitely shouldn't do that. It doesn't do it when I try your function in the same shape query on my system. Is it perhaps a bug fixed in one of the service packs?
You don't actually need a UDF to do this: a CASE in the SUM would work too (and avoid the UDF call overhead):

SUM(CASE WHEN PosType = 'SL' THEN OpenShares ELSE 0.0 END) AS Long,
SUM(CASE WHEN PosType = 'SS' THEN OpenShares ELSE 0.0 END) AS Short

Billpl
Yak Posting Veteran

71 Posts

Posted - 2002-08-30 : 11:45:34

ah, should have checked that first. I appears that the server I was trying this on has not been updated.

Using Case is the best way, the above was only an experiment. But at the same time I lean in favor using UDF's for cleaning up queries when the syntax starts getting out of control even if I might have to give up some overhead. Plus UDF's are reusable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources