How we can use CTE in subquery in sql server?

Question

How we can use a CTE in a subquery in SQL Server?

like:

SELECT id (I want to use CTE here), name FROM table_name

Sigh. Sigh. Why does everyone want so much context? TSQL has a syntax that is set in stone. We don't have to convince SQL server why the results are valuable before we are allowed to execute the statements. The more context you provide on this site, the more likely a moderator won't be able to cut through it and they'll vote to close. Sigh. — Ronnie Overby, Mar 22 '18 at 20:53
Agree totally with @RonnieOverby on this. The context is how to use a CTE inside a subquery (it's in the title). Here's a clearer version: Explain how to execute the following query: SELECT * FROM (WITH cte AS ( _A nice SELECT statement_) SELECT _some fields or calculations or perhaps aggregates_ FROM cte) AS x _possibly JOIN another table_ — Reversed Engineer, Feb 03 '20 at 10:00
Ok, here's some context: I'm building a rules engine in SQL, with the rules defined by the user. So I have a stored procedure which generates SQL dynamically from the rules. The SP returns a generated SQL statement, e.g. WITH cte1 (), cte2 () etc. SELECT ... FROM cte1 JOIN cte2 ON... Now I want another SP to generate a summary of the results e.g. SELECT COUNT(*) FROM (%s) where %s is the SQL generated by the first SP. I'm aware of the security risks of dynamic SQL. Generating SQL on-the-fly is required for this application, namely the rules engine. — Reversed Engineer, Feb 03 '20 at 10:14

score 53 · Answer 1 · answered Dec 16 '09 at 11:47

53

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

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

answered Dec 16 '09 at 11:47

Maximilian Mayerl

11,253
2
33
40

13

This can't mitigate correlated subquery. – Vadzim Oct 07 '14 at 11:16
1

There is no way to pass a condition to the `WITH` statement, so there's no point in doing this. – Silencer Apr 01 '21 at 08:26

score 14 · Answer 2 · edited Dec 13 '12 at 13:16

14

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

edited Dec 13 '12 at 13:16

dzavala

988
10
21

answered Dec 13 '12 at 12:53

Alfred E. Mustermann

149
1
3

4

I have wanted the ability to use a CTE as a derived table many times. If there were a way to do this that didn't involve creating a database object (ad-hoc) I would love it. – Ronnie Overby Mar 22 '18 at 20:54

score 1 · Answer 3 · answered Oct 02 '20 at 23:24

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_CTE2
GO

Now, use it into sub query

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

Thanks [Bhuwan Maharjan](https://stackoverflow.com/users/5000924/bhuwan-maharjan), I was looking to create a view with CTE, this helped. Kudos!!! — bismi, Jul 07 '21 at 06:44
This does not anwer the question. To create a view you need high level permissions. — edc65, Jul 28 '22 at 12:13

How we can use CTE in subquery in sql server?

3 Answers3

Linked