Common Table Expression (CTE)
{ Posted on 10/25/2009 02:43:00 PM
by Silvercode
}
Tags :
SQL SERVER
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
WITH Exmple_CTE (myID, Name, addedDate)
AS
(
SELECT p.myID, p.Name, u.addedDate)
FROM users u innerjoin
profile p on u.myID=p.myID
)
SELECT *
FROM Exmple_CTE
ORDER BY myID ASC,Name ASC, addedDate ASC
GO
This query creates a CTE named Exmple_CTE that returns the name, category name,id and addedDate. Once the CTE has been defined, it must then immediately be used in a query. The query treats the CTE as if were a view or table in the system, returning the three fields defined by the CTE (ProductName, CategoryName, and UnitPrice).
CTE's are very helpful incase you want to do paging using stored procedure methods.
WITH Exmple_CTE (myID, Name, addedDate)
AS
(
SELECT p.myID, p.Name, u.addedDate)
FROM users u innerjoin
profile p on u.myID=p.myID
)
SELECT *
FROM Exmple_CTE
ORDER BY myID ASC,Name ASC, addedDate ASC
GO
This query creates a CTE named Exmple_CTE that returns the name, category name,id and addedDate. Once the CTE has been defined, it must then immediately be used in a query. The query treats the CTE as if were a view or table in the system, returning the three fields defined by the CTE (ProductName, CategoryName, and UnitPrice).
CTE's are very helpful incase you want to do paging using stored procedure methods.
2 Response to "Common Table Expression (CTE)"
grt info never understood ne thing but still looks good grt work keep doin
Ani
thnks
Post a Comment