Working days on SQL Server using CTE in a function

Question

I'm trying to write a function to return the number of working days between 2 dates. I have a table with all the bank holidays in which has two fields: BHID and BHDate.

I've written the following SELECT query to test out my theory and it works nicely:

DECLARE @StartDate AS Date, @EndDate AS Date

SET @StartDate = '2015-01-01'
SET @EndDate = '2015-07-01'

;WITH CTE AS (
    SELECT @StartDate AS StartDate, @EndDate AS EndDate, @StartDate AS DateCalc
    UNION ALL
    SELECT @StartDate AS StartDate, @EndDate AS EndDate, DATEADD(dd, 1, DateCalc) AS DateCalc FROM CTE
    WHERE DATEADD(dd, 1, DateCalc) <= EndDate)

SELECT COUNT(*) AS Days
FROM CTE
LEFT JOIN psd.dbo.LUBankHolidays BH
ON CTE.DateCalc = BH.BHDate
WHERE BHID IS NULL
AND LEFT(DATENAME(dw, DateCalc) ,1) <> 'S'
option (Maxrecursion 0);

But when I try and put it in a function I get some errors around the SET line, it could be me just not being quite as good on functions as the rest of my knowledge or I was wondering whether it was to do with the CTE not having been used ahead of the SET line? Here's my attempt:

CREATE FUNCTION [dbo].[WorkingDays]
(@StartDate AS Date, @EndDate AS Date
)
RETURNS INT
AS
BEGIN

DECLARE @Days AS INT

;WITH CTE AS (
    SELECT @StartDate AS StartDate, @EndDate AS EndDate, @StartDate AS DateCalc
    UNION ALL
    SELECT @StartDate AS StartDate, @EndDate AS EndDate, DATEADD(dd, 1, DateCalc) AS DateCalc FROM CTE
    WHERE DATEADD(dd, 1, DateCalc) <= EndDate)

SET @Days = 
(SELECT COUNT(*) AS Days
FROM CTE
LEFT JOIN psd.dbo.LUBankHolidays BH
ON CTE.DateCalc = BH.BHDate
WHERE BHID IS NULL
AND LEFT(DATENAME(dw, DateCalc) ,1) <> 'S'
option (Maxrecursion 0);)

RETURN @Days

END

I even tried inserting the result of the INT into a temporary table and then dropping this after setting @Days before then returning @Days but this then results in an error regarding not being allowed to produce temporary tables as part of a function.

Any help would be great, I'm sure it's a small thing but just escaping me currently.

Answer 1

Because a CTE can only be used in a query, rather than using SET to set the variable, use SELECT instead:

SELECT @Days = 
(SELECT COUNT(*) AS Days
FROM CTE …

From MSDN:

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.