create table [dbo].[billno](
    [id] [int] identity(1,1) not null,
    [billname] [varchar](20) collate chinese_prc_ci_as not null,
    [maxno] [int] null,
    [maxdate] [varchar](20) collate chinese_prc_ci_as null,
    [prefix] [varchar](20) collate chinese_prc_ci_as null,
    [numbit] [int] null constraint [df_billno_numbit]  default ((0)),
    [style] [varchar](50) collate chinese_prc_ci_as null,
    [memo] [text] collate chinese_prc_ci_as null,
    [createdate] [datetime] null constraint [df_billno_createdate]  default (getdate()),
    [editdate] [datetime] null,
    [state] [int] null constraint [df_billno_state]  default ((0)),
 constraint [pk_billno] primary key clustered 
(
    [id] asc
)with (ignore_dup_key = off) on [primary]
) on [primary] textimage_on [primary]

go

create procedure [dbo].[p_getbillno] --产生按年月日排列的档案号
    @billname varchar(20),
    @no varchar(20) output, --为产生的档案号
    @numbit int = 4 --编号位数

as 
    declare @maxno int,
            @maxdate varchar(20),
            @prefix varchar(20),
            @xnumbit int,
            @currdate varchar(12),
            @fill_zero int,
            @imaxno int

    set @fill_zero = 100000000
    set @currdate = convert(varchar(8), getdate(),112)

    select @maxno = maxno, @maxdate = maxdate, @prefix = prefix, 

  @xnumbit = numbit from billno with(xlock) where billname = @billname 
    if @@rowcount <> 1 return -1

    if @xnumbit <> 0 set @numbit = @xnumbit 

    if @maxdate <> @currdate or @maxdate is null or @maxno is null
        set @imaxno = 1
    else
        set @imaxno = @maxno + 1

    update billno
         set maxno = @imaxno, maxdate = @currdate, editdate = getdate() 
      where billname = @billname
    if @@rowcount <> 1 return -1

    set @no = coalesce(@prefix, '') + @currdate + right(cast(@fill_zero + @imaxno as
    varchar), @numbit) 

    return 1