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