Creating a Recursive using Common Table Expression

Simply speaking, A recursion is nothing but a function which itself call that function repeatedly until that condition satisfies or till the end of the data available to process.

I shall explain with a sample

IF OBJECT_ID('Reports','U') IS NOT NULL
    DROP TABLE dbo.Reports
GO

CREATE TABLE dbo.Reports
(
     ReportID INT PRIMARY
    ,ReportName VARCHAR(100)
    ,FolderID INT NULL
    ,Type VARCHAR(10) NULL
}
GO

INSERT INTO dbo.Reports (ReportID, ReportName, FolderID, Type)
VALUES(1, 'EmployeeReports', NULL, 'FOLDER')
INSERT INTO dbo.Reports (ReportID, ReportName, FolderID, Type)
VALUES(2, 'EmployeeAttendence', 1, 'REPORT')
INSERT INTO dbo.Reports (ReportID, ReportName, FolderID, Type)
VALUES(3, 'EmployeeProgress', 1, 'REPORT')
INSERT INTO dbo.Reports (ReportID, ReportName, FolderID, Type)
VALUES(4, 'EmployeePackage', 1, 'FOLDER')
INSERT INTO dbo.Reports (ReportID, ReportName, FolderID, Type)
VALUES(5, 'LowLevel', 4, 'REPORT')
INSERT INTO dbo.Reports (ReportID, ReportName, FolderID, Type)
VALUES(6, 'HighLevel', 4, 'REPORT')

In the above table, we are going to maintain a hierarchical structure

like FolderID then Corresponding reports inside that Folder

Now i have create the table AdsReports, Now i'm goin to write a select statement which will precede by a WITH Clause which will include a 
Common Table Expression Called MyReports.


WITH
    MyReports (ReportID, ReportName, FolderID, Type, ReportLevel)
AS
    (
     SELECT ReportID, ReportName, FolderID, Type, 1
     FROM AdsReports
     WHERE FolderID IS NULL
     UNION ALL
     SELECT r.ReportID, r.ReportName, r.FolderID, r.Type, a.ReportLevel + 1
     FROM AdsReports r
         INNER JOIN MyReports a
              ON r.FolderID = a.ReportID
    )

SELECT ReportID, ReportName, FolderID, Type
FROM MyReports
ORDER BY ReportLevel, FolderID

Now it's the time to watch the result of the above Query :-)

-----------------------------------------------------------
ReportID        ReportName            FolderID      Type
-----------------------------------------------------------
1               EmployeeReports       NULL          FOLDER
2               EmployeeAttendence    1             REPORT
3               EmployeeProgress      1             REPORT
4               EmployeePackage       1             FOLDER
5               LowLevel              4             REPORT
6               HighLevel             4             REPORT
-----------------------------------------------------------

In the above Query, ReportLevel is the level

if a folder is a root folder, then it's level is 1
if a report is reside inside a root folder the level is 2
suppose if a folder is reside inside a root folder then it's level is 2
if a report reside inside a folder which is under the root folder then it's 
level is 3 like wise the list will go on...

The first query inside a With Clause gets the Root value
Then it will join with the Next query to get the remaining levels...

This is all about Common Table Expression.
This is just a sample, you can use the CTE for many requirements.

Advantage:

The execution time is really less when you use the common table expression
DisAdvantage:

Sometimes if your query is bad, it will lead to indefinite looping and it will
get stuck, To Avoid this you can set a maximum recursion limit.

you can set it in the Query like

WITH
    MyReports (ReportID, ReportName, FolderID, Type, ReportLevel)
AS
    (
     SELECT ReportID, ReportName, FolderID, Type, 1
     FROM AdsReports
     WHERE FolderID IS NULL
     UNION ALL
     SELECT r.ReportID, r.ReportName, r.FolderID, r.Type, a.ReportLevel + 1
     FROM AdsReports r
         INNER JOIN MyReports a
              ON r.FolderID = a.ReportID
    )

SELECT ReportID, ReportName, FolderID, Type
FROM MyReports OPTION (MAXRECURSION 5)
ORDER BY ReportLevel, FolderID

Once the query executed for 5 levels, it would stop automatically.