How We Can Use Cte in Subquery in SQL Server

Sql Server - user CTE in subquery

In SQL Server, CTE's must be at the top of the query. If you construct queries dynamically, you could store a list of CTE's in addition to the query. Before you send the query to SQL server, you can prefix the query with a list of CTE's:

; with Cte1 as (...definition 1...),
  Cte2 as (...definition 2...),
  Cte3 as (...definition 3...),
  ...
...constructed query...

This is assuming that you're constructing the SQL outside of SQL Server.

You could also consider creating views. Views can contain CTE's, and they can be used as a subquery or derived table. Views are a good choice if you generate SQL infrequently, say only during an installation or as part of a deployment.

Subquery in CTE

You don't need a subquery for this, either use a CTE or subquery; you are mixing both of them, just do this:

with temp As
(
  SELECT   StudentID, 
           sum(CASE WHEN CourseStatus =1 then 1 else 0 end) Status
  FROM     StudentFinalResultsDetails
  group by StudentID
) -- You have to select something after the brackets 
select CASE WHEN Status = 0 then 'Passed'
                when status >0 and status < 2 then 'uncomplete'
                else 'Failed' end AS Studentstatus
from temp

Or: remove WITH CTE:

select CASE WHEN Status = 0 then 'Passed'
            when status >0 and status < 2 then 'uncomplete'
            else 'Failed' end Studentstatus
from
(
   SELECT     StudentID, 
           sum(CASE WHEN CourseStatus =1 then 1 else 0 end) Status
   FROM         StudentFinalResultsDetails
   group by StudentID
)As t

---

Update

The problem in the query in your question, you have to move the part:

 select CASE WHEN Status = 0 then 'Passed'
                when status >0 and status < 2 then 'uncomplete'
                else 'Failed' end AS Studentstatus`

to the outer of the brackets of the WITH temp ( .... ) then after it select whatever you want from it.

Because:

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE
statement that references some or all the CTE columns. A CTE can also
be specified in a CREATE VIEW statement as part of the defining SELECT
statement of the view.

In your query, you didn't put any statements after it. See reference

Use CTE instead of Subquery in SQL Server

The only difference between a derived table and a CTE (other than the syntax of course) is that a CTE allows you to write recursive queries which self reference (aka Recursive CTE). It is sometimes cleaner to abstract things into a CTE ahead of time, but it's just a matter of preference, not a performance matter. Personally I tend to be a fan of using CTEs to abstract big ugly queries, but there's not really a strong argument for one over the other.

By way of a trivial example, these two statements are functionally identical

 --- using a CTE
;with numbers as
(
    select top 10 num = row_number() over (order by (select null))
    from sys.all_objects
)
select *
from numbers    

 -- Using a derived table
select *
from
(
    select top 10 num = row_number() over (order by (select null))
    from sys.all_objects
) a

SQL: is there ever any reason to use a subquery instead of a CTE?

Any query that you can write using only subqueries in the FROM clause and regular joins can use CTEs with direct substitution.

Subqueries are needed for:

• Correlated subqueries (which are generally not in the FROM clause).
• Lateral joins (in databases that support LATERAL or APPLY keywords in the FROM clause).
• Scalar subqueries.

Sometimes, a query could be rewritten to avoid these constructs.

Subqueries in the FROM clause (except for lateral joins) can be written using CTEs.

Why are subqueries used and not CTEs? The most important reason is that CTEs are a later addition to the SQL language. With the exception of recursive CTEs, they are not really needed. They are really handy when a subquery is being referenced more than one time, but one could argue that a view serves the same purpose.

As mentioned in the comments, CTEs and subqueries might be optimized differently. This could be a performance gain or loss, depending on the query and the underlying indexes and so on.

Use subquery inside a CTE so that I can use recursion

I thought it was just a bad reference on the JOIN in the recursive part, but that's not quite the issue.

You're referencing CTE, which can be thought of as the previous iteration of the recursion. But for your "current iteration", you're trying to reference ScriptTbl2, but ScriptTbl2 only exists within the "anchor" query - you have to redo that query with ROW_NUMBER(), etc. to build up your own ScriptTbl2 in the recursive part.

Maybe like this:

DECLARE @EndCreateTableScript varchar(20) = ') ON [PRIMARY] ';
DECLARE @NewLine varchar(2) = CHAR(13) + CHAR(10);
DECLARE @createTableScript varchar(max)
SET @createTableScript = 'CREATE TABLE [' 

;WITH CTE
AS (
    SELECT ScriptTbl2.RowNumber, ScriptTbl2.CreateTableStart, ScriptTbl2.ColumnScript, ScriptTbl2.EndTableScript
    FROM
        (
            SELECT ROW_NUMBER() OVER (PARTITION BY TableName ORDER BY TableName) AS RowNumber, TableName, 
                    CreateTableStart, ColumnTextStart + DataSizeText + ColumnNullText + @NewLine AS ColumnScript, @EndCreateTableScript + TextImageScript AS EndTableScript
            FROM
            (
                SELECT DISTINCT SchemaName, TableName, @createTableScript + SchemaName + '].[' + TableName + '] ( ' + @NewLine AS CreateTableStart,
                    '[' +ColName + '] [' + DataType + '] ' AS ColumnTextStart,  
                    CASE WHEN DataType in ('bit', 'int', 'money', 'datetime') THEN ' '
                         WHEN DataType in ('numeric', 'decimal') THEN '(' + DataTypePrecision + ', ' + DataTypeScale + ') '
                         WHEN CAST(DataTypeMaxLength AS INT) = -1 THEN '(max) '
                         WHEN DataType in ('varchar', 'varbinary') THEN '('+ DataTypeMaxLength +') '
                         WHEN DataType = 'nvarchar' THEN  '('+ CAST(CAST(DataTypeMaxLength AS INT)/2 AS varchar(5)) +') '
                    END AS DataSizeText,    
                    CASE IsColumnNullable WHEN '0' THEN 'NOT NULL,' ELSE 'NULL,' END AS ColumnNullText,
                    CASE WHEN TextImageFileGroup IS NOT NULL THEN 'TEXTIMAGE_ON [' + TextImageFileGroup + ']' ELSE '' END AS TextImageScript                                                                                                 
                FROM #DBObjectsToAdd
            ) AS ScriptTbl
        ) AS ScriptTbl2
    WHERE RowNumber = 1
    UNION ALL
    SELECT CTE.RowNumber, CTE.CreateTableStart, CTE.ColumnScript + ' ' + ScriptTbl2.ColumnScript, CTE.EndTableScript 
    FROM CTE JOIN (
        SELECT ScriptTbl2.RowNumber, ScriptTbl2.CreateTableStart, ScriptTbl2.ColumnScript, ScriptTbl2.EndTableScript
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY TableName ORDER BY TableName) AS RowNumber, TableName, 
                    CreateTableStart, ColumnTextStart + DataSizeText + ColumnNullText + @NewLine AS ColumnScript, @EndCreateTableScript + TextImageScript AS EndTableScript
            FROM
            (
                SELECT DISTINCT SchemaName, TableName, @createTableScript + SchemaName + '].[' + TableName + '] ( ' + @NewLine AS CreateTableStart,
                    '[' +ColName + '] [' + DataType + '] ' AS ColumnTextStart,  
                    CASE WHEN DataType in ('bit', 'int', 'money', 'datetime') THEN ' '
                         WHEN DataType in ('numeric', 'decimal') THEN '(' + DataTypePrecision + ', ' + DataTypeScale + ') '
                         WHEN CAST(DataTypeMaxLength AS INT) = -1 THEN '(max) '
                         WHEN DataType in ('varchar', 'varbinary') THEN '('+ DataTypeMaxLength +') '
                         WHEN DataType = 'nvarchar' THEN  '('+ CAST(CAST(DataTypeMaxLength AS INT)/2 AS varchar(5)) +') '
                    END AS DataSizeText,    
                    CASE IsColumnNullable WHEN '0' THEN 'NOT NULL,' ELSE 'NULL,' END AS ColumnNullText,
                    CASE WHEN TextImageFileGroup IS NOT NULL THEN 'TEXTIMAGE_ON [' + TextImageFileGroup + ']' ELSE '' END AS TextImageScript                                                                                                 
                FROM #DBObjectsToAdd
            ) AS ScriptTbl
        ) AS ScriptTbl2
    ) AS ScriptTbl3
    ON ScriptTbl3.RowNumber = CTE.RowNumber + 1 --you want each iteration to increase, right?
)
SELECT *
FROM CTE

---

Related Topics