CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
Mike Marks
Mike Marks

Reputation: 10139

Common Table Expressions - where do we create one at? View, Stored Proc, etc.?

I'm very new to CTE's - when we define a common table expression, do we create it as a stored procedure, a view, etc.? For example, if I have:

USE AdventureWorks2012;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY ManagerID;
GO

Where would I store this? I hope I'm making sense. This is a query that I would like to store somewhere to be reused.

Upvotes: 0

Views: 165

Answers (1)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

You would use a view. Although this is a hierarchical query, it still is just this: a query.

Upvotes: 1

Related Questions