AspBucket offers ASP.NET, C#, VB, Jquery, CSS, Ajax, SQL tutorials. It is the best place for programmers to learn

Tuesday 27 October 2015

Common Table Expressions in Sql Server


Common Table Expressions(CTE) is used when you are working with complex queries. Your requirement is to fetch specific data from tables that is returned after multiple operations like sub query or multiple joints then CTE is useful for you.



Here I am giving you example how to use CTE

Lets You want to fetch records of Employee which Organization name is xyz.
SELECT * FROM  (
        SELECT O.OrgName, E.Name, E.Address From Organization O
        Inner join Employee E on E.OrgId= O.OrgId) tbl
WHERE tbl.OrgName ='xyz'
ORDER BY T.NAME
In first look the query looks complex. It can be simplify by CTE as.
;With Emp(OrgName, Name,Address)  --Column names for Temporary table
AS
(
SELECT O.OrgName, E.Name, E.Address,E.Address From Organization O
Inner join Employee E on E.OrgId= O.OrgId
)
SELECT * FROM Emp  --SELECT for CTE temporary Table
WHERE Emp.OrgName ='xyz'
ORDER BY Emp.NAME
Scope of CTE
;With Emp(OrgName, Name,Address)  --Column names for Temporary table
AS
(
SELECT O.OrgName, E.Name, E.Address,E.Address From Organization O
Inner join Employee E on E.OrgId= O.OrgId
)
SELECT * FROM Organization;
SELECT * FROM Emp  --SELECT for CTE temporary Table
WHERE Emp.OrgName ='xyz'
ORDER BY Emp.NAME ;
Here CTE scope upto Organization.

0 comments :

Post a Comment

  • Popular Posts
  • Comments