How to create recursive query in SQL Server?

• babu.ece

  SSC Veteran

  Points: 248

  More actions

  December 28, 2011 at 4:38 am

  #262229

  Hi Friends,

  I am very new to sql server please tell me how to create recursive query in SQL Server.

  Thanks,

  Mobile Application Development|mhealth

• okbangas

  SSChampion

  Points: 11773

  More actions

  December 28, 2011 at 7:02 am

  #1426063

  No offense intended: Let me Google that for you.

  ---

  
  Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

  Concatenating Row Values in Transact-SQL[/url]

• malleswarareddy_m

  SSCertifiable

  Points: 5847

  More actions

  December 30, 2011 at 2:29 am

  #1426804

  using Common table expressions (CTE).

  Malleswarareddy
  I.T.Analyst
  MCITP(70-451)

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  December 30, 2011 at 2:39 am

  #1426807

  Post some sample data and the expected result - for more information about how best to ask a question, please read the link in my sig.

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• manoj0134

  SSC Rookie

  Points: 32

  More actions

  December 30, 2011 at 9:14 pm

  #1427154

  [font="Courier New"]

  Here is an example of the recursive query.We usually use these types of queries to loop over and generate results

  Generally a CTE is used and then joined with the original table for the recurring action or looping

  CREATE TABLE dbo.MyEmployees

  (

  EmployeeID smallint NOT NULL,

  FirstName nvarchar(30) NOT NULL,

  LastName nvarchar(40) NOT NULL,

  Title nvarchar(50) NOT NULL,

  DeptID smallint NOT NULL,

  ManagerID int NULL,

  CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)

  );

  -- Populate the table with values.

  INSERT INTO dbo.MyEmployees VALUES

  (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)

  ,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)

  ,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)

  ,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

  ,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274);

  insert into dbo.MyEmployees

  Select 287,N'Manoj',N'k',N'Customer Representative',3,286;

  select * from dbo.MyEmployees

  USE AdventureWorks

  GO

  With tableCTE(ManagerID,EmployeeID,Title,DeptID,Level)

  AS

  (

  -- Anchor member definition

  SELECT e.ManagerID, e.EmployeeID, e.Title,e.DeptID,0 AS level

  FROM dbo.MyEmployees AS e

  WHERE e.ManagerID IS NULL

  UNION ALL

  -- Recursive member definition

  SELECT p.ManagerID, p.EmployeeID, p.Title,p.DeptID,Level+1

  FROM dbo.MyEmployees AS p

  INNER JOIN tableCTE AS d

  ON p.ManagerID = d.EmployeeID

  )

  -- Querying the CTE to get the result

  Select ManagerID,EmployeeID,Title,DeptID,level

  from tableCTE

  [font][font="Courier New"][/font][/font]