Use of CTE(Common Table Expression).

vishalneeraj-24503
Posted by vishalneeraj-24503 under Sql Server category on | Points: 40 | Views : 360
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;

Comments or Responses

Login to post response