Print numbers from 1 to 10 using CTE

CTE stands for "Common Table Expression". A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE or DELETE statement that immediately follows the CTE.

The syntax of the CTE as follows

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

We can print the numbers by using CTE very easily.

WITH CTEName

AS

(

SELECT COUNT=1

UNION ALL

SELECT COUNT=COUNT+1

FROM CTEName where COUNT<10

)

SELECT COUNT FROM CTEName