【Vegas原创】按自定义格式进行编号的SQL自定义函数

生成格式如：DT.EMP.0000000001的自增emp_id, 加入EmpBaseINfo表中。
 
--生成格式如DT.EMP.0000000001  【Vegas Add】
ALTER FUNCTION [dbo].[Get_EmpBaseInfo_AccountID](@RowID as int)
RETURNS nvarchar(50) as 
begin


    declare @oid nvarchar(50)

    declare @headStr nvarchar(50)


    set @headStr='DT.EMP.'    

----------------------------RowID的计算--------------------------------------
    if exists(select 1 from empbaseinfo)

        --如empbaseinfo存在数据，count(*)后直接加RowID
        begin

            select  @oid=count(1) from empbaseinfo  

            set @oid=@oid+ @RowID 

        end

    else 

        set @oid=@RowID

       
----------------------------补全十个数------------------------------
    declare @str nvarchar(50) --临时编号


    set @str=convert(nvarchar,(convert(int,right(@oid,10))))  


    while (10-len(@str)>0)

        begin

             set @str='0'+@str    

        end


    set @oid=@headStr+@str

---------------------------返回值---------------------------------------------------------
RETURN @oid
end      
 
 
 调用：
 
selectdbo.Get_EmpBaseInfo_AccountID(ROW_NUMBER() OVER(ORDER BY hbdwno)) as id,
        hbdwno,hbdbno,hbdenm,hbdcnm,

        (case hbdgdr when 1 then 'M' when 0 then 'F' end),

        (case hbdidl when 1 then 'IDL' when 0 then 'DL' end),

        hbdwdt,hbdbir,hbdmds,'DT' as domain
from hrmsdt.hrms.dbo.hrshhbd
order by hbdwno