Common Table Expressions (CTE)

In Stored procedures and in our SQL Statement sometimes we need to use same query again and again. In that case you can use CTE.

USE AdventureWorks2012
GO

--Defining CTE
WITH Empl
AS
(
    SELECT * FROM HumanResources.Employee
)

--Using CTE
SELECT Empl.* FROM Empl
GO

And you can use several CTE’s in one statement.

USE AdventureWorks2012 GO

--Defining CTE

WITH Empl AS ( SELECT * FROM HumanResources.Employee ),

Pers AS ( SELECT * FROM Person.Person )

--Using CTE

SELECT Empl.*, Pers.*

FROM Empl INNER JOIN Pers

ON Empl.BusinessEntityID = Pers.BusinessEntityID

GO

Updated : At the very begining I though that CTE were reusable again and again inside a one batch. But it was a myth. I realized it after posting this post, but forgot to remove that today I removed that.