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: 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 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 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 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.I am a great believer in luck, and I find the harder I work the more I have of it. Stephen LeacockForum Etiquette: How to post data/code on a forum to get the best help
Post #1473379

 Permissions