sql 根据子级ID获取所有父级

CREATE FUNCTION [dbo].[f_GetParentCode](@id int)
  RETURNS @re TABLE(id int,pid int,level int)
  AS
  begin
  declare @level int
  set @level = 1
  declare @pid int
  select @pid = ParentCode from T_GWZJ_CityMapping where HCityCode = @id
  insert @re
  select HCityCode,ParentCode,@level from T_GWZJ_CityMapping where HCityCode = @id
  set @level = @level + 1
  insert @re
  select HCityCode,ParentCode,@level from T_GWZJ_CityMapping where HCityCode = @pid
  while @@rowcount > 0  
  begin
  set @level = @level + 1
  select @pid = ParentCode from T_GWZJ_CityMapping where HCityCode = @pid
  insert @re
  select HCityCode,ParentCode,@level from T_GWZJ_CityMapping where HCityCode = @pid
  end
  return
  end

GO