sql函数的简单实用

alter function F_SortList(@sortID varchar(50)) returns varchar(50)
as
begin
declare @error int
declare @strTemp nvarchar(200)
declare @depthTemp int
declare @mark int
declare @sortValue varchar(50)
declare @sortPath varchar(50)

if exists (select column_depth from sort where column_id =@sortID )
begin
select @depthTemp=column_depth from sort where column_id =@sortID
set @strTemp=''
set @sortValue=''
set @mark=@depthTemp
while (@depthTemp>0)
begin
select @sortID=parent_id ,@strTemp =column_name +' >> '+@strTemp,@sortValue=isnull(column_id,'')+'-'+@sortValue from sort where column_id =@sortID
set @depthTemp=@depthTemp-1
end
if(@mark=3)
begin
set @sortValue= substring(@sortValue,1,len(@sortValue)-1)
set @strTemp= substring(@strTemp,1,len(@strTemp)-2)
end
else if(@mark=2)
begin
set @sortValue= @sortValue+' '
set @strTemp= substring(@strTemp,1,len(@strTemp)-2)
end
set @sortPath =@strTemp
end
else
begin
set @sortPath ='0'
end
return @sortPath
end
go