changing the function in the select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

changing the function in the select statement

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2014-05-16 : 20:26:42

Hi,

I have a view which uses the function to select particular column in the select statement and hence its giving the performance issue for us.

I am trying to replace the function with inline query,could you please help me here.

select statement:
,DML.GetSubscriptionLength(SD.SubscriptionStartDate,SD.SubscriptionEndDate) AS SubscriptionLength

Function:

ALTER FUNCTION [DML].[GetSubscriptionLength]
(
@DateA DATETIME,
@DateB DATETIME
)
RETURNS INT
AS
BEGIN
Declare @NoOfMonths Int
SET @NoOfMonths=Convert(int,Round(DATEDIFF(dd,@DateA,@DateB)/30.0,0))
SELECT @NoOfMonths= CASE WHEN @NoOfMonths>=12 AND @NoOfMonths<24 THEN 12
WHEN @NoOfMonths>=24 AND @NoOfMonths<36 THEN 24
WHEN @NoOfMonths>=36 THEN 36
ELSE @NoOfMonths
END
Return @NoOfMonths

END

Thanks,
Gangadhara MS
SQL Developer and DBA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-16 : 23:40:54

Try changing to an in-line table valued function.

this code assumes a table called subscriptionTable with a primary key of subscriptionID


--select statement:

select st.[subscriptionid]
       ,sl.[SubscriptionLength]
from SubscriptionTable st
join tvf_SubscriptionLength() sl
       on sl.subscriptionid = st.subscriptionid
where ...

--function:
go
create function tvf_SubscriptionLength()
returns table
as return 
(
select SD.SubscriptionID
       ,case 
        when convert(int, round(datediff(dd, SD.SubscriptionStartDate,SD.SubscriptionEndDate) /30.0,0)) < 12 
        then convert(int, round(datediff(dd, SD.SubscriptionStartDate,SD.SubscriptionEndDate) /30.0,0))
        
        when convert(int, round(datediff(dd, SD.SubscriptionStartDate,SD.SubscriptionEndDate) /30.0,0)) < 24
        then 12
        
        when convert(int, round(datediff(dd, SD.SubscriptionStartDate,SD.SubscriptionEndDate) /30.0,0)) < 36
        then 24
        
        else 36
        end as [SubscriptionLength]
from   SubscriptionTable SD
)
go

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources