---- GetObjWorkTreeDepth ----
  -- возвращает глубину дерева работ для заданного объекта
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetObjWorkTreeDepth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetObjWorkTreeDepth]
GO
create function GetObjWorkTreeDepth(@ObjID integer)
returns int
as
begin 
  declare @result int
  declare @CurDepth int
  declare @CurWorkID int
  set @result = 0
  set @CurDepth = 0
  declare Cur cursor for SELECT Код_работы FROM Работа WHERE Код_объекта = @ObjID  AND dbo.IsTopworkInObjTree(Код_работы) = 1
  open Cur
  fetch next from Cur into @CurWorkID
  while @@fetch_status = 0
  begin
    set @CurDepth = dbo.GetWorkTreeDepth(@CurWorkID)
    if @CurDepth > @result set @result = @CurDepth
    fetch next from Cur into @CurWorkID
  end
  return @result
end
