Stored Procedure using Common Table Expression

You can use the Common Table Expression (CTE) to create a Stored procedure. While CTE is only in memory the Stored Procedure is persisted.
In order to create a stored procedure I will be using the same Common Table Expression used in my two earlier posts here and here.

Run the following in the query pane of SQL Server Management Studio (herein SSMS 2012).
----
USE Northwind
GO
CREATE PROCEDURE dbo.GetName
@city varchar(10)
AS
WITH NCTE(FirstName, LastName,City)
  as

 (SELECT FirstName,LastName,City
 FROM Employees
 )
SELECT *
FROM  NCTE
WHERE city=@city
-----
After the statement is run you will find the stored procedure as shown (you may have to refresh the stored procedures node in the Object Explorer:

---
CTEStoredProc_00

In order to run the stored procedure execute the following statement in the query pane:
Exec GetName 'London'

You get the following query response:

CTEStoredProc_01