存在未有终点的数据,需要归位赋值
CREATE Proc up_AreaNavForArea
(
@Area_ID int,
@TextNav varchar(200) out,
@HtmlNav varchar(200) out
)
As
Declare @tmpTextNav varchar(200)
Declare @tmpHtmlNav varchar(200)
Set @tmpTextNav = ''
Set @tmpHtmlNav = ''
Declare @tmpArea_ID int
Declare @tmpParent_ID int
Declare @tmpChsName varchar(100)
Select @tmpParent_ID = [Parent_ID] , @tmpChsName = [ChsName] From [Area] Where [Area_ID] = @Area_ID
Set @tmpHtmlNav = '<a href="url'+convert(varchar(8),@Area_ID)+'html">'+@tmpChsName+'</a>'
Set @tmpTextNav = @tmpChsName
while( @tmpParent_ID > 0 )
Begin
Select @tmpArea_ID=[Area_ID] , @tmpParent_ID = [Parent_ID] , @tmpChsName = [ChsName] From [Area] Where [Area_ID] = @tmpParent_ID
Set @tmpHtmlNav = '<a href="url'+convert(varchar(8),@tmpArea_ID)+'html">'+@tmpChsName + '</a> >> ' + @tmpHtmlNav
Set @tmpTextNav = @tmpChsName + ' >> ' + @tmpTextNav
set @tmpParent_id = 0 --这个不可少
End
Set @TextNav = @tmpTextNav
Set @HtmlNav = @tmpHtmlNav
select @textnav,@htmlnav
GO
CREATE Proc up_AreaNavForArea
(
@Area_ID int,
@TextNav varchar(200) out,
@HtmlNav varchar(200) out
)
As
Declare @tmpTextNav varchar(200)
Declare @tmpHtmlNav varchar(200)
Set @tmpTextNav = ''
Set @tmpHtmlNav = ''
Declare @tmpArea_ID int
Declare @tmpParent_ID int
Declare @tmpChsName varchar(100)
Select @tmpParent_ID = [Parent_ID] , @tmpChsName = [ChsName] From [Area] Where [Area_ID] = @Area_ID
Set @tmpHtmlNav = '<a href="url'+convert(varchar(8),@Area_ID)+'html">'+@tmpChsName+'</a>'
Set @tmpTextNav = @tmpChsName
while( @tmpParent_ID > 0 )
Begin
Select @tmpArea_ID=[Area_ID] , @tmpParent_ID = [Parent_ID] , @tmpChsName = [ChsName] From [Area] Where [Area_ID] = @tmpParent_ID
Set @tmpHtmlNav = '<a href="url'+convert(varchar(8),@tmpArea_ID)+'html">'+@tmpChsName + '</a> >> ' + @tmpHtmlNav
Set @tmpTextNav = @tmpChsName + ' >> ' + @tmpTextNav
set @tmpParent_id = 0 --这个不可少
End
Set @TextNav = @tmpTextNav
Set @HtmlNav = @tmpHtmlNav
select @textnav,@htmlnav
GO
--你程序出现死循环的原因在于你的判断方式
--下面的在不存在[Parent_ID] = [Area_ID]的情况下可以了
CREATE Proc up_AreaNavForArea
(
@Area_ID int,
@TextNav varchar(200) out,
@HtmlNav varchar(200) out
)
As
Declare @tmpTextNav varchar(200)
Declare @tmpHtmlNav varchar(200)
Set @tmpTextNav = ''
Set @tmpHtmlNav = ''
Declare @tmpArea_ID int
Declare @tmpParent_ID int
Declare @tmpChsName varchar(100)
Select @tmpParent_ID = [Parent_ID] , @tmpChsName = [ChsName] From [Area] Where [Area_ID] = @Area_ID
Set @tmpHtmlNav = '<a href="url'+convert(varchar(8),@Area_ID)+'html">'+@tmpChsName+'</a>'
Set @tmpTextNav = @tmpChsName
while exists(select 1 from [Area] where [Area_ID] = @tmpParent_ID)
Begin
Select @tmpArea_ID=[Area_ID] , @tmpParent_ID = [Parent_ID] , @tmpChsName = [ChsName] From [Area] Where [Area_ID] = @tmpParent_ID
Set @tmpHtmlNav = '<a href="url'+convert(varchar(8),@tmpArea_ID)+'html">'+@tmpChsName + '</a> >> ' + @tmpHtmlNav
Set @tmpTextNav = @tmpChsName + ' >> ' + @tmpTextNav
End
Set @TextNav = @tmpTextNav
Set @HtmlNav = @tmpHtmlNav
select @textnav,@htmlnav
GO