“Must declare the table variable” Error with CTE

Question

I am getting following errors when I use a table variable inside a CTE in SQL Server 2005. When I use a physical table, it works fine.

Must declare the table variable "@Employee".
Incorrect syntax near ')'.

Code:

DECLARE @Emploee TABLE (EmpID INT, EmpName VARCHAR(50), Dept VARCHAR(5), Manager INT)

INSERT INTO @Emploee VALUES (1, 'A', NULL,NULL)
INSERT INTO @Emploee VALUES (2, 'B', 'D100',NULL)

;WITH EmployeeManagers AS 
(
  SELECT e.EmpId, e.EmpName, 1 AS Level
       , e.Manager, e.EmpID CurrLevelEmpID, Cast(Null as VarChar(5)) Dept
  FROM   @Employee AS e
  WHERE  e.Manager IS NOT NULL

  UNION ALL

  SELECT em.EmpId, em.EmpName, Level + 1 AS Level
       , e.Manager, e.EmpID CurrLevelEmpID, e.Dept Dept
  FROM   EmployeeManagers em
         INNER JOIN Employee e ON e.EmpId = em.Manager
)
SELECT * FROM EmployeeManagers

How can we fix this error?

REFERENCE

How to use table variable in a dynamic sql statement?

You've misspelled the @Emploee table at the top - try adding the missing y and rerunning — AHiggins, Apr 28 '14 at 19:28
This question appears to be off-topic because it is too localized. — ypercubeᵀᴹ, Apr 29 '14 at 15:52
This was due to a typo from my side in table creation. I would like this to be deleted. — LCJ, Apr 29 '14 at 17:45

score 3 · Accepted Answer · answered Apr 28 '14 at 19:33

DECLARE @Emploee TABLE (EmpID INT, EmpName VARCHAR(50), Dept VARCHAR(5), Manager INT)

INSERT INTO @Emploee VALUES (1, 'A', NULL,NULL)
INSERT INTO @Emploee VALUES (2, 'B', 'D100',NULL)

;WITH EmployeeManagers AS 
(
  SELECT e.EmpId, e.EmpName, 1 AS Level
       , e.Manager, e.EmpID CurrLevelEmpID, Cast(Null as VarChar(5)) Dept
  FROM   @Emploee AS e         --<-- Wrong Spellings here
  WHERE  e.Manager IS NOT NULL

  UNION ALL

  SELECT em.EmpId, em.EmpName, Level + 1 AS Level
       , e.Manager, e.EmpID CurrLevelEmpID, e.Dept Dept
  FROM   EmployeeManagers em
         INNER JOIN @Emploee  e ON e.EmpId = em.Manager  --<-- Mising @ sign here
)
SELECT * FROM EmployeeManagers

Also both of your employees has no manager to them, so this query will not return any data with these two records. — M.Ali, Apr 28 '14 at 19:38

score 1 · Answer 2 · answered Apr 28 '14 at 19:30

1

You have a typo:

DECLARE @Emploee TABLE (EmpID INT, EmpName VARCHAR(50), Dept VARCHAR(5), Manager INT)

INSERT INTO @Emploee VALUES (1, 'A', NULL,NULL)
INSERT INTO @Emploee VALUES (2, 'B', 'D100',NULL)

Should be @Employee

answered Apr 28 '14 at 19:30

Marshall Tigerus

3,675
10
37
67

“Must declare the table variable” Error with CTE

2 Answers2