Use of With...As clause Script in T-SQL

First create one table for employee..

USE [test]

GO

/****** Object:  Table [dbo].[employee]    Script Date: 07/12/2008 14:03:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[employee](

[empid] [int] NOT NULL,

[empname] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,

[parentid] [int] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

then enter some records in above table...

1 Tejas 0

2 Anil  3

3 Paresh 5

4 Manish 1

5 Lafdo 4

6 Yashesh 2

-----------------------------------------------------------------------------------------------------------------

then execute below query to find upper-level parents and low-level parents...

select * from dbo.employee;

with RecursionCTE (empid,empname,parentid,upperlevel)

 as

 (

   select empid,empname,parentid,

          convert(varchar(100),'') upperlevel

      from dbo.employee

      where parentid = 0

   union all

   select R1.empid,R1.empname,

          R1.parentid,

          case when DataLength(R2.upperlevel) > 0

                    then convert(varchar(100),(select empname from dbo.employee where empid = R1.empid)

       + ' -> '+ R2.upperlevel )

                    else (select empname from dbo.employee where empid = R1.empid)

                    end as upperlevel

      from dbo.employee as R1

     join RecursionCTE as R2 on R1.parentid = R2.empid

  )

select empid,empname,parentid,upperlevel from RecursionCTE order by empid asc ;

with RecursionCTE (empid,empname,parentid,lowerlevel)

 as

 (

   select parentid,empname,empid,

          convert(varchar(100),'') lowerlevel

      from dbo.employee

   union all

   select R1.empid,R1.empname,

          R1.parentid,

          case when DataLength(R2.lowerlevel) > 0

                    then convert(varchar(100),(select empname from dbo.employee where empid = R1.empid)

       + ' -> '+ R2.lowerlevel )

                    else (select empname from dbo.employee where empid = R1.empid)

                    end as lowerlevel

      from dbo.employee as R1

     join RecursionCTE as R2 on R1.empid = R2.parentid

  )

select empid,empname,parentid,max(lowerlevel) as lowerlevel

from RecursionCTE 

where datalength(lowerlevel) > 0

group by empid,empname,parentid order by empid asc 

-----------------------------------------------------------------------------------------------------------------

Output will be...

All records of above table..

1 Tejas 0

2 Anil 3

3 Paresh 5

4 Manish 1

5 Lafdo 4

6 Yashesh 2

All records with upper-level parents..

1 Tejas 0

2 Anil 3 Anil -> Paresh -> Lafdo -> Manish

3 Paresh 5 Paresh -> Lafdo -> Manish

4 Manish 1 Manish

5 Lafdo 4 Lafdo -> Manish

6 Yashesh 2 Yashesh -> Anil -> Paresh -> Lafdo -> Manish

All records with low-level parents..

1 Tejas 0 Tejas -> Manish -> Lafdo -> Paresh -> Anil -> Yashesh

2 Anil 3 Anil -> Yashesh

3 Paresh 5 Paresh -> Anil -> Yashesh

4 Manish 1 Manish -> Lafdo -> Paresh -> Anil -> Yashesh

5 Lafdo 4 Lafdo -> Paresh -> Anil -> Yashesh

6 Yashesh 2 Yashesh