Thursday, May 24, 2012

CTE recursive Query in SQL Server 2008 with Stored Procedure

Following is the complete script for making a recursive CTE call to table. There are two queries in the bottom which shows you how it works and one SP example

USE [master]

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