Programming The Life

Suppose you have a query that returns a data set and you want for each row returned you want to invoke a function or query that returns a table (table-valued function or query ), the new APPLY operator allowa you to do this for example if you have a table Employees(EmpID,Name,ManagerID) and you have the following function that takes an Employee id as a parameter and returns a table of employees under this employee and their level in the hierarchy CREATE FUNCTION [dbo].[GetEmps](@EmpID [int]) RETURNS @RES TABLE ( Empid INT , EmpName nvarchar(50), Level INT ) AS begin with Emps_CTE (EmpID,Name,Level) as ( select EmpID,Name,0 as Level FROM Employees WHERE ManagerID is null and EmpID=@EmpID Union All Select Employees.EmpID,Employees.Name,Level + 1 FROM Employees Join Emps_CTE ON Emps_CTE.EmpID=Employees.ManagerID ) INSERT INTO @RES select * FROM Emps_CTE return The following query will return for each employee the employees working under him and their level SELECT Employ

A CTE is composed of an expression name, optional column list, and a defining query. Once defined, the CTE may be referenced in the executing SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement for example: use Northwind; with Customers_CTE as ( select ContactName,CompanyName from Customers ) select * from Customers_CTE the beautiful thing about CTEs is that you can use them to create recursive queries say you have the following table Employees(EmpID,Name,ManagerID) and you want to select each employee and his level in the employees hierarchy (with 0 as the top level) this can be accomplished with the following CTE With Emps_CTE (EmpID,Name,Level) As ( SELECT EmpID,Name, 0 AS Level FROM Employees WHERE ManagerID IS NULL Union All Select Employees.EmpID,Employees.Name,Emps_CTE.Level+1 as Level FROM Emps_CTE join Employees on Emps_CTE.EmpID=Employees.ManagerID ) Select * FROM Emps_CTE Note that the defining query must contain at least two SELECT statements combined by a UNION ALL op

Channel 9 is celebrating its first birthday

"If builders built buildings the way that programmers wrote programs, the first woodpecker that came along would destroy civilization" - Weinberg’s Second Law Dijkstra, A discipline of programming, 1976