Optimize procedure

Question

Hi 

 

I have one procedure and inside procedure call function. Which are take more time .

 

I want to remove finction and retrive function value with select statment with my query. So please suggest how can do this.

 

My function :

 

Create Function dbo.fn_GetWorkDays(

@startDate DateTime

,@endDate DateTime

,@excludeHolidays INT = 0

)

Returns int

BEGIN

-- DECLARE INTERNAL VARIABLES

DECLARE @fdow INTEGER

DECLARE @ldow INTEGER

DECLARE @wdays INTEGER

DECLARE @first_date DATETIME

DECLARE @last_date DATETIME

DECLARE @days INTEGER

-- Make sure the earlier date is assigned to @first_date

IF ( @startDate <= @endDate )

BEGIN

SET @first_date = CONVERT(DATETIME, CONVERT(VARCHAR(10), @startDate, 110))

SET @last_date = CONVERT(DATETIME, CONVERT(VARCHAR(10), @endDate, 110))

END

ELSE

BEGIN

SET @first_date = CONVERT(DATETIME, CONVERT(VARCHAR(10), @endDate, 110))

SET @last_date = CONVERT(DATETIME, CONVERT(VARCHAR(10), @startDate, 110))

END

-- Obtain the day of week for the first and last date

SET @fdow = DATEPART( DW, @first_date )

SET @ldow = DATEPART( DW, @last_date )

SET @days = DATEDIFF( DD, @first_date, @last_date ) + 1

SET @wdays = @days - (((@days + (@fdow - 3)) / 7) * 2) -

((1 - (@fdow / 7)) * ((9 - @fdow) % 7 % 6 % 5 % 4 % 3)) -

((@ldow + 1) % 7 % 6 % 5 % 4 % 3)

-- This section will be implemented when we decide to exclude Holidays

IF (@excludeHolidays = 1)

BEGIN

SET @wdays = @wdays - ISNULL((Select count(holiday_date) from Holidays (nolock)

Where holiday_date between @startdate and @enddate

and DatePart(dw, holiday_date) between 2 and 6), 0)

END

RETURN Case When @wdays < 0 Then 0 Else @wdays End

END

 

 

 

Any one update please.....