Bill Greer Bill Greer - 7 months ago 10
SQL Question

Assign INT Variable From Select Statement in SQL

Why do I get a syntax error on the following SQL statements:

DECLARE @Count90Day int;

SET @Count90Day = SELECT COUNT(*) FROM Employee WHERE DateAdd(day,30,StartDate) BETWEEN
DATEADD(day,-10,GETDATE()) AND DATEADD(day,10,GETDATE()) AND Active ='Y'


I am trying to assign the number of rows returned from my Select statement to the variable @Count90Day.

Answer

You need parentheses around the subquery:

DECLARE @Count90Day int;

SET @Count90Day =  (SELECT COUNT(*)
                    FROM Employee
                    WHERE DateAdd(day,30,StartDate) BETWEEN DATEADD(day,-10,GETDATE()) AND 
                                                            DATEADD(day,10,GETDATE())  AND
                          Active ='Y'
                   );

You can also write this without the set as:

DECLARE @Count90Day int;

SELECT @Count90Day =  COUNT(*)
FROM Employee
WHERE DateAdd(day,30,StartDate) BETWEEN DATEADD(day,-10,GETDATE()) AND DATEADD(day,10,GETDATE()) AND
      Active ='Y';