Automatically generate numbered methods
Foreword: Use many serial numbers, Indentity or New ID (), is not easy to use, write one, this series is characterized by taking the largest number of the system in the system in the table, if there is no record on the day, Automatically generate a day number
1. Create a seed table, this table is used to save the maximum serial number currently used by each table - seed table Create Table Seed (BM VARCHAR (20) Not Null, - Name Bh varchar (12) Not null, - Seed No. Constraint PK_SEED Primary Key (BM))
2. When we built a new table, record this table name into the seed table, such as: - Add Record INSERT INTO SEED (BM, BH) VALUES ('TableName') Goes
3. In the database built a storage process, automatically generate new numbers, this number takes the day, so many times when querying some days of records, this serial number is very useful - automatically generates numbers for a table for parameters. IF EXISTS (select * from sysobjects where name = 'proc_getbh') drop procedure proc_getbh gocreate procedure proc_getbh @BM varchar (20) asdeclare @BH char (12) declare @TODAY char (8) beginselect @ TODAY = convert (char (8), getdate (), 112) SELECT @ BH = BH from seed where bm = @ Bmif @BH is Null or left (@ BH, 8) <> @ todaybeginselect @ BH = @ Today '0000'ENEND SELECT @ BH = Left (@BH , 8) Right (CHAR (4), Convert (int, Right (@ BH, 4))) 1), 4) Update seed set bh = @ Bh where bm = @ bmselect @BH as bhend
4. Examples are as follows: 'Automatically generate new numbers to table xxx = conn.execute ("proc_getbh @ BM =' xxx '), RS (" BH ") is the new number you get
