The main issue here is that you are using an iterative approach instead of a set-based approach. Also, inline table valued functions are going to perform much better than scalar functions.
I've rewritten your function as an inline table-valued function. I also used a CTE to create a tally table on the fly. If you already have a tally table (or Itzik Ben Gan's tally table function) you can skip the CTE.
I've also used OFFSET/FETCH to return the desired date.
CREATE FUNCTION [dbo].[fn_ADD_WORKING_DAYS] (
) RETURNS TABLE WITH SCHEMABINDING
WITH Tally_Base AS
FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0),(0), (0) ) b(n)
, Tally AS
SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) n
FROM Tally_Base A
CROSS JOIN Tally_Base B
/* Add as many cross joins as necessary to get the maximum number of records you will need. */
/* If you already have a tally table in your database, you can skip the above CTE. */
CROSS APPLY( VALUES(DATEADD(DAY, n - 1, @Date))) dt(dt)
WHERE dt.dt NOT IN ( SELECT Dt_Holiday FROM Tbl_Holidays )
AND DATEPART(WEEKDAY, dt) NOT IN (1, 7)
ORDER BY dt.dt
OFFSET @NDAYS - 1 ROWS
FETCH NEXT 1 ROWS ONLY
J. Drew Allen
Business Intelligence Analyst
How to post data/code on a forum to get the best help.How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT
options when you are writing a post.