Used many serial number method, indentity or New ID (), are not good to use, they wrote a, this serial number is characterized by: every time the corresponding table of the system day maximum number, if no record on the day, then automatically generate a day ordinal.
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 NOT NULL,--table name
BH varchar (a) NOT NULL,--seed number
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.
