How we can use CTE in subquery in sql server?

sql sql-server tsql sql-server-2005 common-table-expression
Paresh picture Paresh · Dec 16, 2009 · Viewed 41.5k times · Source

How we can use CTE in subquery in sql server?

like ..

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

Answer

Maximilian Mayerl picture Maximilian Mayerl · Dec 16, 2009

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

Related questions

Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon

Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure WITH SomeClause1 AS ( SELECT .... ) WITH SomeClause2 AS ( SELECT .... ) But the error occurs Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an …

Read More
How can I use if statement after a CTE (SQL Server 2005)

Last night I was writing a simple T-SQL program something like this DECLARE @ROLEID AS INT SELECT @ROLEID = [ROLE ID] FROM TBLROLE ;WITH CTE AS ( SELECT * FROM SOMETABLE ) IF (@ROLEID = 1) BEGIN //SOMECODE END ELSE IF(@ROLEID = 2) BEGIN //SOMECODE END ELSE …

Read More
SQL 2005 CTE vs TEMP table Performance when used in joins of other tables

I have a complex query that I need to use in a subsequent query (actually update statement). I have tried both using a CTE and a temp table. The performance using the CTE is horrible vs the temp table approach. …

Read More