create function [dbo].[AutoHousingEstate_ID]
returns nvarchar(3)
as
begin
    declare @i int
    set @i=1
    declare @HousingEstate_ID nvarchar(3)
    select @HousingEstate_ID=isnull(min(HousingEstate_ID),'001') from HousingEstateInfo
    if @i<999
    while convert(int,@HousingEstate_ID)<>@i
        begin
        select @HousingEstate_ID=right('000'+convert(nvarchar(3),@i),3)
        set @i=@i+1
        end
    return (@HousingEstate_ID)
end

SELECT RIGTH('000',ISNULL(MIN(CAST(HousingEstate_ID AS INT)),1),3) FROM HousingEstateInfo a WHERE NOT EXISTS(SELECT 1 FROM HousingEstateINfo WHERE CAST(HousingEstate_ID AS INT)=CAST(a.HousingEstate_ID AS INT)+1) ORDER BY HousingEstate_ID

------解决方案--------------------
SQL code
create function [dbo].[AutoHousingEstate_ID]()
returns varchar(3)
as
begin   
    declare @HousingEstate_ID varchar(3)
    select top 1 @HousingEstate_ID=HousingEstate_ID from HousingEstateInfo H
    where not exists(select 1 from HousingEstateInfo where HousingEstate_ID=right('00'+convert(varchar(3), cast(H.HousingEstate_ID as int)+1),3))
    order by HousingEstate_ID    
    return (right('00'+convert(varchar(3), cast(H.HousingEstate_ID as int)+1),3))
end