Friday, May 20, 2016

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


PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...