Sql Server 2008

创建函数：
create function sector_pid(@id varchar(10)) returns @t_level table(id varchar(10))
as
begin
  insert into @t_level select @id
  select @id  = sct_pid  from sector where sct_id = @id and sct_pid not in(1)
  while @@ROWCOUNT > 0
  begin
    delete from @t_level
    insert into @t_level select @id
    select @id = sct_pid  from sector where sct_id = @id and sct_pid not in(1)
  end
  return
end
go






create function Sector_Name(@sctId varchar(10))
returns varchar(50)
as
begin
declare @ccode varchar(50)
declare @name  varchar(50)
declare @pid   varchar(10)
declare @sctpid varchar(10)
declare @spid   varchar(10)

select @pid=SCT_PID  from SECTOR where SCT_ID=@sctId
if(@pid=1)
   set @ccode=(select SCT_NAM from SECTOR where SCT_ID=@sctId)
else if(@pid=0)
   set @ccode=(select SCT_NAM from SECTOR where SCT_ID=@sctId)
else
begin
   select @name  =SCT_NAM  from SECTOR where SCT_ID=@sctId
   select @sctpid=SCT_PID  from SECTOR where SCT_ID=@pid
   if (@sctpid=1)
set @ccode =(select SCT_NAM  from SECTOR where SCT_ID = @pid )+'-'+@name
   else
    begin
   while @sctpid <>1
begin
select @spid=SCT_PID  from SECTOR where SCT_ID = @sctpid
set @ccode =(select SCT_NAM  from SECTOR where SCT_ID = @sctpid )+'-'+@name
set @sctpid=@spid
if @sctpid=1
break
end 
end
end
return @ccode
end
go