GO
/****** Object: Database [Recursive] Script Date: 05/24/2012 09:14:14 ******/
CREATE DATABASE [Recursive] ON PRIMARY
( NAME = N'Recursive', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Recursive.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Recursive_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Recursive_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Recursive] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Recursive].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Recursive] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Recursive] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Recursive] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Recursive] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Recursive] SET ARITHABORT OFF
GO
ALTER DATABASE [Recursive] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Recursive] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Recursive] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Recursive] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Recursive] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Recursive] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [Recursive] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Recursive] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Recursive] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Recursive] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Recursive] SET DISABLE_BROKER
GO
ALTER DATABASE [Recursive] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Recursive] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Recursive] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Recursive] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Recursive] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Recursive] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Recursive] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Recursive] SET READ_WRITE
GO
ALTER DATABASE [Recursive] SET RECOVERY FULL
GO
ALTER DATABASE [Recursive] SET MULTI_USER
GO
ALTER DATABASE [Recursive] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Recursive] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'Recursive', N'ON'
GO
USE [Recursive]
GO
/****** Object: Table [dbo].[Employee] Script Date: 05/24/2012 09:14:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
[EmployeeId] [bigint] IDENTITY(1,1) NOT NULL,
[FirstName] [nchar](20) NULL,
[LastName] [nchar](20) NULL,
[ManagerId] [bigint] NULL,
[DepartmentName] [nchar](10) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (1, N'Bill ', N'Gates ', NULL, N'OliveView ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (2, N'Susan ', N'Soma ', 1, N'IS ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (3, N'Rose ', N'Field ', 2, N'IS ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (4, N'Jack ', N'Wellsoft ', 3, N'IS ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (5, N'Dandy ', N'Sun ', 4, N'IS ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (6, N'Jaweed ', N'Sarfraz ', 4, N'IS ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (7, N'Chris ', N'Leero ', 2, N'HIMS ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (8, N'Christpopher ', N'Rad ', 7, N'HIMS ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (9, N'Pauly ', N'Pryan ', 7, N'HIMS ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (10, N'Rosy ', N'River ', 7, N'HIMS ')
INSERT [dbo].[Employee] ([EmployeeId], [FirstName], [LastName], [ManagerId], [DepartmentName]) VALUES (11, N'Val ', N'Valrie ', 4, N'IS ')
SET IDENTITY_INSERT [dbo].[Employee] OFF
/****** Object: StoredProcedure [dbo].[getAllEmployee] Script Date: 05/24/2012 09:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[getAllEmployee]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
With EMP_CTE AS
(SELECT [EmployeeId]
,[FirstName]
,[LastName]
,[ManagerId]
,[DepartmentName]
FROM [Recursive].[dbo].[Employee]
where ManagerId is null
union all
SELECT e.[EmployeeId]
,e.[FirstName]
,e.[LastName]
,e.[ManagerId]
,e.[DepartmentName]
FROM [Recursive].[dbo].[Employee] e
inner Join Emp_CTE ecte on ecte.EmployeeId = e.ManagerId
)
Select * from EMP_CTE
order by 4
END
GO
/****** Object: ForeignKey [FK_Employee_Employee] Script Date: 05/24/2012 09:14:14 ******/
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Employee] FOREIGN KEY([ManagerId])
REFERENCES [dbo].[Employee] ([EmployeeId])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Employee]
GO
------------------------------------------------------------------- Query with Immediate Boss Name
WITH
cteReports (EmpID, FirstName, LastName, ManagerID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 0
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
r.EmpLevel + 1
FROM Employee e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
)
SELECT
rtrim(ltrim(FirstName)) + ' ' + rtrim(ltrim(LastName)) AS FullName,
EmpLevel,
(SELECT rtrim(ltrim(FirstName)) + ' ' + rtrim(ltrim(LastName)) FROM Employee
WHERE EmployeeID = cteReports.ManagerID) AS Manager
FROM cteReports
ORDER BY EmpLevel, ManagerID
---------------------------------------------------------------- Simple Query
With EMP_CTE AS
(SELECT [EmployeeId]
,[FirstName]
,[LastName]
,[ManagerId]
,[DepartmentName], 0 AS Level
FROM [Recursive].[dbo].[Employee]
where ManagerId is null
union all
SELECT e.[EmployeeId]
,e.[FirstName]
,e.[LastName]
,e.[ManagerId]
,e.[DepartmentName], Level +1
FROM [Recursive].[dbo].[Employee] e
inner Join Emp_CTE ecte on ecte.EmployeeId = e.ManagerId
)
Select * from EMP_CTE
order by 4
