Use a WITH in an SQL function?

Question

I'm trying to make a function but I do not know how to get the WITH in it. Here is my code.

CREATE FUNCTION CubicVolume  (@StartDate date, @EndDate date) RETURNS @TableDays TABLE
(Days int)
AS
BEGIN
    INSERT @TableDays
       WITH Dates AS  (
                SELECT @StartDate AS DayInQuestion
                    UNION ALL
                SELECT DATEADD(Day, 1, DayInQuestion) AS DayInQuestion
                    FROM Dates AS Dates
                    WHERE (DayInQuestion < @EndDate)
                )
    SELECT DISTINCT count(Dates.DayInQuestion)
        FROM Dates AS Dates LEFT OUTER JOIN
        HEATHrs ON Dates.DayInQuestion = HEATHrs.StartDate
        WHERE        (CAST(DATEPART(weekday, Dates.DayInQuestion) AS int) BETWEEN 2 AND 6)
   RETURN
END

In SQL Server, the `with` goes before the `insert`. This is not true in all databases. — Gordon Linoff, Jan 28 '15 at 14:36

score 2 · Answer 1 · answered Jan 28 '15 at 14:33

You have to put the Common Table Expression (CTE) before the INSERT:

WITH Dates AS  
(
    SELECT @StartDate AS DayInQuestion
        UNION ALL
    SELECT DATEADD(Day, 1, DayInQuestion) AS DayInQuestion
        FROM Dates AS Dates
        WHERE (DayInQuestion < @EndDate)
)
INSERT @TableDays
SELECT DISTINCT count(Dates.DayInQuestion)
    FROM Dates AS Dates LEFT OUTER JOIN
    HEATHrs ON Dates.DayInQuestion = HEATHrs.StartDate
    WHERE        (CAST(DATEPART(weekday, Dates.DayInQuestion) AS int) BETWEEN 2 AND 6)

Use a WITH in an SQL function?

1 Answers1