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
WITH Empl AS ( SELECT * FROM HumanResources.Employee ),
Pers AS ( SELECT * FROM Person.Person )
SELECT Empl.*, Pers.*
FROM Empl INNER JOIN Pers
ON Empl.BusinessEntityID = Pers.BusinessEntityID
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.