In dealing with the holidays, rather than searching for each day in the loop why not just find the next holiday after the start and then check the loop date for a match against that.

Taking the original function-

CREATE function fn_AddBizDays

--Accepts Date ‘X’ and Integer ‘Y’

(

@dtBeginDate datetime,

@iBizDays int

)

RETURNS datetime

--Result is Date ‘Z’ that is ‘Y’ business days after supplied Date ‘X’

AS

BEGIN

DECLARE @dtEndDate datetime

DECLARE @dtDateHolder datetime

DECLARE @dtNextHol datetime

SET @dtDateHolder = @dtBeginDate

SET @dtEndDate = DATEADD(d,@iBizDays,@dtBeginDate)

--Find the first Public Holiday Date

select @dtNextHol = MIN(holiday_date) FROM tblHolidays where holiday_date >=@dtDateHolder

WHILE (@dtDateHolder <= @dtEndDate)

BEGIN

--Is the date being checked a Saturday or Sunday?

IF((DATEPART(dw, @dtDateHolder) in (1, 7))

--Is the date being checked a holiday?

OR (DATEADD(dd, 0, DATEDIFF(dd, 0, @dtDateHolder))=@dtNextPH))

--NOTE: DATEDIFF trick used above to discard TIMESTAMP

BEGIN

-- Extend the date range for Weekends and Holidays by one day

SET @dtEndDate = DATEADD(d,1,@dtEndDate)

if (DATEADD(dd, 0, DATEDIFF(dd, 0, @dtDateHolder))=@dtNextHol)

BEGIN

-- get the next public holiday date

select @dtNextHol = MIN(holiday_date ) FROM tblHolidays where holiday_date >@dtDateHolder

END

END

--Move to the next day in the range and loop back to check it

SET @dtDateHolder = DATEADD(d,1,@dtDateHolder)

END

--Respond with newly determined end date

RETURN @dtEndDate

END