How to automatically generate numbering for ASP

1. Build the seed table, which is used to save the maximum number of numbers that are currently used by the tables.

--seed table

create table SEED (
BM varchar(20) not null, --表名
BH varchar(12) not null, --种子编号
constraint PK_SEED primary key(BM)
)
go

2. When we build a new table, we also record the table name in the seed table, such as:

--Adding records to the table in the seed

Insert into SEED (BM,BH) VALUES (' tablename ', ' 200211070000 ')

Go

3. In the database to build a stored procedure, automatically generate a new number, this number to take the day, so many times when querying some days of records, this number is very useful.

--Automatically generate number for a table passed by a parameter

if exists (select * from sysobjects where name='proc_getbh')
drop procedure proc_getbh
go
create procedure proc_getbh @BM varchar(20)
as
declare @BH char(12)
declare @TODAY char(8)
begin
select @TODAY=convert(char(8),getdate(),112)
select @BH=BH from SEED where BM=@BM
if @BH is null or left(@BH,8)<>@TODAY
begin
select @BH=@TODAY+'0000'
end
select @BH=left(@BH,8)+ right('0000' + ltrim(convert(char(4),convert(int,right(@BH,4)))+1),4)
update SEED set BH=@BH where BM=@BM
select @BH AS BH
end

4. Examples are as follows:

' Automatically generate new number for table xxx '

Set Rs=conn.execute ("proc_getbh @BM = ' xxx '")

In this way, RS ("BH") is the new number you get. (Aspcool)