How to use CTE within a stored procedure

Simple Example of using CTE on a stored procedure

Temp tables and variable tables consume more resources so a CTE can be use to improve performance and reduce resource utilization.


USe AdventureWorks2008R2
go
create procedure spPersonNames

AS

;with CTE_Person
(
FirstName
)
 AS
(
SELECT FirstName
FROM Person.Person
)
select *
from CTE_Person