logo
  • Home
  • My Works
  • Downloads
  • Personal
  • Guest Book
  • About Me

CTE (Common Table Expression) – Use recursively

Published by Rafeeque B Muhammad on Saturday, March 6, 2010 12:00 AM
Updated on Saturday, March 6, 2010 12:00 AM


 

The basic CTE syntax is a WITH clause that is used as a preface to a SELECT, INSERT, UPDATE or DELETE statement. It’s we can use a temporary view; also we can use this for recursive traversal.

eg: I will just show how we can display 1 to 100 using CTE recursion

declare @cnt int = 0;

 

WITH DisplayNumberics(Nos)

as

(

      select @cnt

      union all

      select Nos+1 from DisplayNumberics where Nos < 200

)

select * from DisplayNumberics

      OPTION (MAXRECURSION 200)

 

By default SQL Server will take the maximum recursion 100 times. If you want to make it more we should use OPTION (MAXRECURSION value)

 

Please note for recursive implementation the UNION ALL is important. A recursive function can be implemented using a special form of CTE that combines two select statements with a UNION ALL. So it is compulsory to have a top level UNION ALL operator for recursive common table expressions. Also one ‘;’ (semicolon) is compulsory for SQL Server before starting WITH clause, look at green highlight in the examples.

Another example: Cumulative sum

 

WITH DisplayNumberics(Nos,CumulativeSum)

as

(

      select 0,1

      union all

      select Nos+1,CumulativeSum + Nos from DisplayNumberics where Nos < 200

)

select * from DisplayNumberics

      OPTION (MAXRECURSION 200)

 

Technical Articles

  • All
  • .NET Framework
  • ASP.NET
  • C#
  • Win Forms
  • WCF
  • SharePoint(WSS 3.0)
  • SQL Server
  • TSQL
  • Oracle
  • PL/SQL
  • Performance and Tuning
  • Application and Tools
  • Outlook programming
  • HTML/HTML5/XHTML/DHTML/JAVAScript/CSS
  • Windows Services
  • Web Services
  • Security
  • Web
  • General
  • Architecture

Technical Books

  • All
  • C# Threading
  • WCF

Other Links

  • Important Links

Techies Craft © 2014 | Privacy Policy