Use of CTE(Common Table Expression).

CTE is nothing but a Temporary Table in Sql Server just works like Views.When creating CTE,WITH keyword id used.We can also pass parameters in CTE.

Note:- Whatever parameters passed in CTE have to be there in select statement.Otherwise it will not execute.

We can understand CTE with simple example:-

1st way parameter-less CTE:-

With O 
As
(
    Select E.EMPLOYEE_NAME,E.PAN_NUMBER,E.CREATED_DATE
    From EMPLOYEE_MASTER E
    Order by CREATED_DATE Desc
)

Select * From O Order by CREATED_DATE;

2nd way CTE with parameter:-

With O(EMPLOYEE_NAME,PAN_NUMBER,CREATED_DATE) 
As
(
    Select E.EMPLOYEE_NAME,E.PAN_NUMBER,E.CREATED_DATE
    From EMPLOYEE_MASTER E
    Order by CREATED_DATE Desc
)

Select * From O Order by CREATED_DATE;