How to create view with a declare-CodePudding

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_NonApprovedOrWithdrawIntegrityTests AS
DECLARE @StartDate DATETIME = (SELECT MIN(ActionOn) FROM WorkFlowDetails) 
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?

CodePudding user response：

As I mention in the comments, you can't define a variable in a VIEW. A VIEW must be made up of a single statement that results in a SELECT; variables therefore can't be defined as that's 2 statements.

As I also mention, I suggest against an rCTE for creating a calendar. They are slow and if your results need more than 100 rows you may well get errors if you don't define the MAXRECURSION value in your OPTION clause of the outer query.

Instead use an inline tally or a calendar table to get the needed dates. I use a tally here, but I personally recommend the latter (though with no sample data, this is untested):

CREATE OR ALTER VIEW vw_NonApprovedOrWithdrawIntegrityTests AS

    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        UNION ALL
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3), --1,000 rows
    Calendar AS(
        SELECT TOP (SELECT DATEDIFF(MONTH,MIN(ActionOn),GETDATE()) FROM dbo.WorkFlowDetails)
               EOMONTH(DATEADD(MONTH,T.I,WFD.ActionOn)) AS EndOfMonth
        FROM Tally T
             CROSS APPLY (SELECT MIN(ActionOn) FROM dbo.WorkFlowDetails) WFD)
    SELECT ...;

CodePudding user response：

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()