0

I have a table

id name parentid
----------------
1  a     0
2  b     1
3  c     2
4  d     1

Now I want to calculate level with

  • if direct parent id count = 6 then level1,
  • if have 6 level1 count then level2,
  • if have 6 level2 count then level3, and so on

I am using SQL Server 2005 Express

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rohan
  • 157
  • 1
  • 15

2 Answers2

1

You have to use sql recursion query may be this help you http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

Bhavin Chauhan
  • 1,950
  • 1
  • 26
  • 47
0

This should do the job: Simply jump to the parent until you reach the top and count the amount of iterations.

create function dbo.CalcLevel (@ID int)
returns int
as
begin
  declare @level int=0
  while @ID != 0 begin
    select @ID=parentID from MyTable where ID=@ID
    set @level = @level + 1
    if (@level = 1000) set @ID = 0 -- compensate endless loop
  end
  return @level
end
alzaimar
  • 4,572
  • 1
  • 16
  • 30
  • Hi, I executed it is returning errors. Msg 102, Level 15, State 1, Procedure CalcLevel, Line 3 Incorrect syntax near '('. Msg 139, Level 15, State 1, Procedure CalcLevel, Line 0 Cannot assign a default value to a local variable. Msg 139, Level 15, State 1, Procedure CalcLevel, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Procedure CalcLevel, Line 12 Msg 137, Level 15, State 2, Procedure CalcLevel, Line 13 Msg 137, Level 15, State 2, Procedure CalcLevel, Line 15, Must declare the scalar variable "@level". – Rohan Mar 14 '13 at 10:00