Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
3.9k views
in SQL Queries by 21 20 26

I got an error "Common table expression defined but not used." when using CTE in SQL query

This is my SQL Query

With EmployeeCount(DepartmentId, TotalEmployees)
as
(
 Select [Department_ID], COUNT(*) as TotalEmployees
 from Employee
 group by [Department_ID]
)

select GETDATE()
 Select Department_Name , TotalEmployees from Department
join EmployeeCount
on Department.ID = EmployeeCount.DepartmentId
order by TotalEmployees

 


1 Answer

0 like 0 dislike
by 24 26 40
edited by

CTE in SQL

  • CTE stands for common table expression.
  • A CTE is a temporary result set, that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, that immediately follows the CTE.
  • It's strongly recommended to use common table expressions rather than to use subqueries because common table expressions are more readable

Note: A CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

CTE Syntax

The syntax of a CTE in SQL Server:

WITH expression_name[(column_name ,..)]
AS
    (CTE_definition)
SQL_statement;

SQL Common table expression defined but not used

Regarding the error message, this error occurs because you have select GETDATE () between The CTE and select Statement.

If you don’t ask, the answer is always NO!
...