-- Description: 获取项目最大编号(时间) 根据年月日自动生成改日期的最大编号
-- =============================================
--exec [up_Tb_Project_GetMaxProCode] '20160503'
ALTER PROCEDURE [dbo].[up_Tb_Project_GetMaxProCode]
@time nvarchar(30)
AS
declare @returnvalue nvarchar(30)
set @returnvalue='';
BEGIN
if exists(select ProCode as ProCode from dbo.Tb_Project where Isdel=1)--------------存在项目编号
begin
select @returnvalue=MAX(ProCode) from dbo.Tb_Project where Isdel=1 -----------------获取最大值 例:201605031
if(SUBSTRING(@returnvalue,1,8)=@time) ------------判断是否含有改日期的记录,有
set @returnvalue=CONVERT(nvarchar(30),(CONVERT(int,@returnvalue)+1)) -------------------201605032
else
begin
select @returnvalue=MAX(ProCode) from dbo.Tb_Project where Isdel=1 and (SUBSTRING(ProCode,1,8)=@time) -------------获取的当日记录最大编号
if @returnvalue is null or @returnvalue='' ---------------------没有当日记录或者当日记录为空
set @returnvalue=@time+'1'; --------------------201605031
else
set @returnvalue=CONVERT(nvarchar(30),(CONVERT(int,@returnvalue)+1)) -------------201605032
end
end
else------------------不存在项目编号
set @returnvalue=@time+'1'
Select @returnvalue as ProCode from dbo.Tb_Project where Isdel=1
END
-- =============================================
--exec [up_Tb_Project_GetMaxProCode] '20160503'
ALTER PROCEDURE [dbo].[up_Tb_Project_GetMaxProCode]
@time nvarchar(30)
AS
declare @returnvalue nvarchar(30)
set @returnvalue='';
BEGIN
if exists(select ProCode as ProCode from dbo.Tb_Project where Isdel=1)--------------存在项目编号
begin
select @returnvalue=MAX(ProCode) from dbo.Tb_Project where Isdel=1 -----------------获取最大值 例:201605031
if(SUBSTRING(@returnvalue,1,8)=@time) ------------判断是否含有改日期的记录,有
set @returnvalue=CONVERT(nvarchar(30),(CONVERT(int,@returnvalue)+1)) -------------------201605032
else
begin
select @returnvalue=MAX(ProCode) from dbo.Tb_Project where Isdel=1 and (SUBSTRING(ProCode,1,8)=@time) -------------获取的当日记录最大编号
if @returnvalue is null or @returnvalue='' ---------------------没有当日记录或者当日记录为空
set @returnvalue=@time+'1'; --------------------201605031
else
set @returnvalue=CONVERT(nvarchar(30),(CONVERT(int,@returnvalue)+1)) -------------201605032
end
end
else------------------不存在项目编号
set @returnvalue=@time+'1'
Select @returnvalue as ProCode from dbo.Tb_Project where Isdel=1
END