CTE in SQL

A CTE is basically a disposable view. It only persists for a single statement, and then automatically disappears.
Your options include:

• Redefine the CTE a second time. This is as simple as copy-paste from WITH... through the end of the definition to before your SET.
• Put your results into a #temp table or a @table variable
• Materialize the results into a real table and reference that
• Alter slightly to just SELECT COUNT from your CTE:
  
  
  Common Table Expression (CTE) has become very popular these days. However, many users of CTE still have a myth that the t-sql written inside the CTE will be executed only once irrespective of the number of times it is referred in the subsequent CTEs or the related query.
  However, this is not correct. It is a pure misconception. And let’s try to prove this with a simple example.
  
  

  Common Table Expression Syntax

  A Common Table Expression contains three core parts:
  
• The CTE name (this is what follows the WITHkeyword)
• The column list (optional)
• The query (appears within parentheses after the ASkeyword)

The query using the CTE must be the first query appearing after the CTE.

When to Use Common Table Expressions

Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system. Even when a CTE is not necessarily needed, it can improve readability. In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs:

• Create a recursive query.
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.