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