Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert scalar function to Table Valued func Expand / Collapse
Author
Message
Posted Sunday, July 14, 2013 9:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 PM
Points: 342, Visits: 884

How can I convert the below function to return table. I am thinking of putting in cross apply rather than calling scalar function as this is getting expensive call.

CREATE function [dbo].[myfunc]
( @a int, @b int, @c money)
returns money
Begin
declare d as money

set d = -(select isnull(sum(d.cola),0)
from (Select distinct c.cola
from tab1 b
join tab2 c
on b.cola = c.colb
where b.cola <= @b and
b.colb = @a) c
join tab3 d
on c.cola = d.colb and
@a <> d.cola)+@c
return d
End
Post #1473362
Posted Sunday, July 14, 2013 1:09 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 4,046, Visits: 9,203
Without more information, I would say you could do it like this.
CREATE function [dbo].[myfunc]
( @a int, @b int, @c money)
returns table
AS
return
select d = (isnull(sum(d.cola),0)+@c) * -1
from (Select distinct c.cola
from tab1 b
join tab2 c
on b.cola = c.colb
where b.cola <= @b and
b.colb = @a) c
join tab3 d
on c.cola = d.colb and
@a <> d.cola





Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473379
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse