callin functions inside queries

im trying to make this function in tsql

create function isolatedLeptons
(@.idevent INT)
Returns TABLE
AS
Return select l.*
from lepton as l, event as e
where e.id = l.eventid and pt(l.id) > 7.0 and abs(eta(l.id))<2.4
END

GO

select * from lepton;

but it trow me this error

Msg 195, Level 15, State 10, Procedure isolatedLeptons, Line 9
'pt' is not a recognized built-in function name.

pt is defined as

create function pt
(@.idpt INT)
Returns Real
AS
BEGIN
Return ( select sqrt(a.px*a.px + a.py*a.py)
from abstractparticle as a
where @.idpt = a.id)
END

GO

Hi,

you need to use dbo.pt(l.id).

--
Regards,
Daniel Kuppitz

|||

Please do not use scalar UDFs for simple tasks like this. It is overkill in terms of performance and probably manageability too. Use the query expression inline like:

create function isolatedLeptons
(@.idevent INT)
Returns TABLE
AS
Return select l.*
from lepton as l, event as e
where e.id = l.eventid

and (select sqrt(a.px*a.px + a.py*a.py)
from abstractparticle as a
where a.id = l.id) > 7.0

and abs(eta(l.id))<2.4

Or if you want to go the UDF route use an inline TVF instead like:

create function pt
(@.idpt INT)
returns table
AS
return ( select sqrt(a.px*a.px + a.py*a.py) as val
from abstractparticle as a
where @.idpt = a.id)
GO

create function isolatedLeptons
(@.idevent INT)
Returns TABLE
AS
Return select l.*
from lepton as l, event as e
where e.id = l.eventid

and (select a.val

from pt(l.id)) > 7.0

and abs(eta(l.id))<2.4