Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Convert scalar function to Table Valued func Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, July 14, 2013 9:26 AM
 Old Hand Group: General Forum Members Last Login: Tuesday, November 22, 2016 1:37 PM Points: 388, Visits: 1,074
 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 moneyBegindeclare d as moneyset 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)+@creturn dEnd
Post #1473362
 Posted Sunday, July 14, 2013 1:09 PM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 1:48 PM Points: 8,233, Visits: 17,809
 Without more information, I would say you could do it like this.`CREATE function [dbo].[myfunc]( @a int, @b int, @c money)returns tableASreturn 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.General Disclaimer: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?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1473379

 Permissions