How to declare variable from WITH ... SELECT statement

Question

I have a problem with variable definition in MS SQL SERVER. Could you give me any hint, how can I assign the result of count function to variable @WorkingDays ?

DECLARE @StartDate DATE = '2017-03-29';
DECLARE @EndDate DATE = '2017-05-05';
DECLARE @WorkingDays INT;

    WITH DateRange(DateData) AS 
    (
        SELECT @StartDate as Date
        UNION ALL
        SELECT DATEADD(d,1,DateData)
        FROM DateRange 
        WHERE DateData < @EndDate
    )
    SELECT COUNT(DateData)
    FROM DateRange
    WHERE DATEPART(dw, DateData) not in (1, 7)
    OPTION (MAXRECURSION 0)

I`ve tried to use

set @WorkingDays = (WITH.... OPTION (MAXRECURSION 0) )

but it returns an error (incorrect syntax)

I will be grateful for any hint

Best regards

`set @x = (select y from t)` or `select @x = y from t`. Read http://stackoverflow.com/questions/3945361/set-versus-select-when-assigning-variables — Nikhil Vartak, Apr 11 '17 at 10:14

score 4 · Accepted Answer · answered Apr 11 '17 at 09:55

4

SELECT @WorkingDays = COUNT(DateData) ...

answered Apr 11 '17 at 09:55

Arvo

10,349
1
31
34

score 1 · Answer 2 · answered Apr 11 '17 at 10:12

1

you can follow this method:

set @workingDays = (SELECT COUNT(DateData) FROM DateRange
                   WHERE DATEPART(dw, DateData) not in (1, 7)
                   OPTION (MAXRECURSION 0))

answered Apr 11 '17 at 10:12

Mr.Bhanushali

126
12

How to declare variable from WITH ... SELECT statement

2 Answers2