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: Saturday, April 12, 2014 10:28 AM
Points: 313, Visits: 809

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:27 PM
Points: 2,763, Visits: 5,899
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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