Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: 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 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 Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy 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 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.
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse