O'Reilly logo
  • Dustin Waybright thinks this is interesting:

IF OBJECT_ID('HR.GetManagers', 'IF') IS NOT NULL DROP FUNCTION HR.GetManagers; GO CREATE FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE AS RETURN WITH EmpsCTE AS ( SELECT empid, mgrid, firstname, lastname, 0 AS distance FROM HR.Employees WHERE empid = @empid UNION ALL SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance FROM EmpsCTE AS S JOIN HR.Employees AS M ON S.mgrid = M.empid ) SELECT empid, mgrid, firstname, lastname, distance FROM EmpsCTE; GO

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

OBJECT_ID('HR.GetManagers','IF') HR.GetManagers = Table and IF = database. DROP deletes the object if it exists (OBJECT_ID IS NOT NULL), in this case the FUNCTION. Then a CREATE FUNCTION turns empid as INT and sets it to RETURN a Table from EmpsCTE where the INT empid is specified in the select statement.