How to create view with a declare

Question

I need to create a view out of a query I wrote. This issue is that this one contains a declare and a CTE. Now I know that the CTE should be fine but that the declare raises an issue. A part of the code is:

CREATE OR ALTER VIEW vw_NonApprovedTests AS
DECLARE @StartDate DATETIME = (SELECT MIN(ActionOn) FROM WFD) 
DECLARE @EndDate DATETIME = GETDATE();
WITH OrderDays as 
( 
    SELECT 
        CalendarDate = @StartDate
    UNION ALL 
    SELECT 
        CalendarDate = DATEADD(MONTH, 1, CalendarDate)
    FROM 
        OrderDays WHERE DATEADD (MONTH, 1, CalendarDate) <= @EndDate 
),
Calendar AS
(
    SELECT
        EndOfMonth =  EOMONTH (CalendarDate) 
    FROM
        OrderDays                 
) 

SELECT etc.......

The error it gives is:

Msg 156, Level 15, State 1, Procedure vw_NonApprovedOrWithdrawIntegrityTests, Line 6 [Batch Start Line 0]
Incorrect syntax near the keyword 'DECLARE'.

I use this to calculate certain results in the rest of the query. These results need to be put in a view so they can be used in PowerBI for a dashboard. Is there a way for me to make this work?

Answer 1

You can do this by just replacing your variables with your statements that assign them, i.e.

WITH OrderDays AS 
(   
    SELECT CalendarDate  = MIN(ActionOn) 
    FROM   WorkFlowDetails 
    UNION ALL 
    SELECT CalendarDate = DATEADD(MONTH, 1, CalendarDate)
    FROM   OrderDays 
    WHERE  DATEADD (MONTH, 1, CalendarDate) <= GETDATE()
), ....

As has been suggested in the comments though, a Calendar table is going to make this a lot easier and a lot more performant:

SELECT  Date
FROM    dbo.Calendar
WHERE   Date >= (SELECT MIN(ActionOn) FROM WorkFlowDetails) 
AND     Date <= GETDATE()