生成格式如: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 @headStr nvarchar(50)
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))))
begin
set @str='0'+@str
end
RETURN @oid
end
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
调用:
select dbo.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
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
参考文档:http://www.cnblogs.com/ringwang/archive/2008/05/14/1197065.html
相关文章
暂无评论...