一个级联关系的表，向上获取各个字段名的函数

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        
-- Create date: 
-- Description:    返回地区的级联表示,如： 广东 -- 深圳 -- 龙岗
-- =============================================
ALTER FUNCTION [dbo].[getAreaName] 
(
    -- Add the parameters for the function here
    @fareacode numeric(18,0)
)
RETURNS varchar(200)
AS
BEGIN
    declare @returnName varchar(200) -- 返回字符串
    declare @nextAreaCode numeric(18, 0)
    declare @tempName varchar(200) -- 临时存放名称
    
    set @tempName = ''
    set @returnName = ''

    set ANSI_NULLS OFF

    select @tempName = fareaname, @nextAreaCode = fparentcode from tbarea where fareacode = @fareacode
    set @returnName = @tempName

    while (@nextAreaCode <> -1)
    begin
        select @tempName = fareaname, @nextAreaCode = fparentcode from tbarea where fareacode = @nextAreaCode
        set @returnName = @tempName + ' -- ' + @returnName
    end

    set ANSI_NULLS ON
    return @returnName
END