How we can use CTE in subquery in sql server?

Just define your CTE on top and access it in the subquery?

WITH YourCTE(blubb) AS(    SELECT 'Blubb')SELECT id,       (SELECT blubb FROM YourCTE),       nameFROM   table_name

It doesn't work:

select id (I want to use CTE here), name from table_name

It's not possible to use CTE in sub queries.

You can realize it as a work around:

CREATE VIEW MyCTEView AS ..here comes your CTE-Statement.

Then you are able to do this:

select id (select id from MyCTEView), name from table_name

Create a view with CTE/ Multiple CTEs with UNION sets of all CTEs

CREATE VIEW [dbo].[_vEmployees] AS     WITH     TEST_CTE(EmployeeID, FirstName, LastName, City, Country)        AS (            SELECT EmployeeID, FirstName, LastName, City, Country FROM Employees WHERE EmployeeID = 4        ),     TEST_CTE2        AS (            SELECT EmployeeID, FirstName, LastName, City, Country FROM Employees WHERE EmployeeID = 7        )    SELECT EmployeeID, FirstName, LastName, City, Country FROM TEST_CTE UNION SELECT * FROM TEST_CTE2GO

Now, use it into sub query

SELECT * FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM _vEmployees)