自动生成编号

CREATE PROCEDURE [Creat_Table_Code]
 @TableName1 varchar(50), --表名
 @DateString varchar(50),--时间:格式090807
 @CodeString varchar(50),--编码开始字符如:PS
 @Column1 varchar(50)--编码的列名
AS
 begin transaction
  declare @sql1 nvarchar(4000)
  declare @CodeNo int
  set @sql1 = 'select @a=isnull(MAX(convert(INT,REPLACE('+@Column1+', '''+@CodeString+@DateString+''',''''))),0)+1  from ' + @TableName1+'  where  ISNUMERIC(REPLACE('+@Column1+', '''+@CodeString+@DateString+''',''''))=1 AND  '+@Column1+' like '''+@CodeString+@DateString+'%'''
  exec sp_executesql @sql1,N'@a int output',@CodeNo output
  if @CodeNo <10
   begin
    select  @CodeString+@DateString+'000'+convert(varchar,@CodeNo)

   end
  else if @CodeNo <100
   begin
    select  @CodeString+@DateString+'00'+convert(varchar,@CodeNo)
   end
  else if @CodeNo <1000
   begin
    select  @CodeString+@DateString+'0'+convert(varchar,@CodeNo)
   end
  ELSE
   begin
    select  @CodeString+@DateString+convert(varchar,@CodeNo)
   end

  
  if(@@error=0 and @@rowcount=1)
       commit transaction
    else
       rollback transaction

 

 

 

--Author: Sum.zheng
--CreateDate:2011-05-19
--Function: 自动生成订车单号(日期加序号编号)
CREATE PROCEDURE Get_oddNo

@type varchar (10),
@time smalldatetime

AS

BEGIN

DECLARE @T_type VARCHAR(10)

DECLARE @T_time VARCHAR(10)

DECLARE @NEW_ID VARCHAR(16)

--取出表中当前日期的已有的最大ID

IF @type = '0'
 BEGIN
  SET @T_type = 'LH'
 END
ELSE IF @type = '1'
 BEGIN
  SET @T_type = 'CK'
 END
ELSE IF @type = '2'
 BEGIN
  SET @T_type = 'WL'
 END
ELSE
 BEGIN
  SET @T_type = 'WL'
 END

set @T_time = right(convert(varchar,@time,112),6)

SET @NEW_ID = NULL
set @NEW_ID = ( SELECT TOP 1 [odd_no] FROM [a_enternote_dv] WHERE [odd_no] LIKE @T_type+@T_time+'%' ORDER BY [odd_no] DESC )
--如果未取出来

IF @NEW_ID IS NULL

--说明还没有当前日期的编号，则直接从1开始编号
BEGIN
SET @NEW_ID = (@T_type+@T_time+'01')

--如果取出来了
END
ELSE

BEGIN

DECLARE @NUM VARCHAR(8)

--取出最大的编号加上1

SET @NUM = CONVERT(VARCHAR, (CONVERT(INT, RIGHT(@NEW_ID, 2)) + 1))

--因为经过类型转换，丢失了高位的0，需要补上

SET @NUM = REPLICATE('0', 2 - LEN(@NUM)) + @NUM

--最后返回日期加编号

SET @NEW_ID =@T_type+ @T_time + @NUM
END
SELECT @NEW_ID as odd_no
END

GO