函数调试有关问题

函数调试问题

SQL code

create function [dbo].[AutoHousingEstate_ID]returns nvarchar(3)asbegin    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

SQL code

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)asbegin       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