Automatically generate document number

A data table is specially designed to store the latest document numbers for various documents in the management software. Write a stored procedure to automatically generate document numbers.

 

--****************
-- Computing ID

-- Use the // table-level exclusive lock // prevents users from simultaneously modifying the record
--****************
Create procedure GETID
@ XH integer,
@ ID integer out
As
/*
If you want to lock the entire table in connection 1 and do not allow other transactions to update any records in the table, but you can read the records, you can use the holdlock option, that is, (holdlock is equivalent to serializable)

SQL Server's concurrent processing is controlled by its own lock. It seems to be concurrent. In fact, there is a waiting queue phenomenon, but the time interval is short, so when the number of concurrency is large, we still need to design a manual lock.

Place a range lock on the dataset to prevent other users from updating the dataset or inserting rows into the dataset before the transaction is completed. This is the most restrictive level among the four isolation levels. Because-the sending level is low, this option should be used only when necessary.

If you want to lock the entire table in the connection and do not allow other transactions to update any records in the table or even read any records in the table, you can use the tablockx option,

If you want to allow other transactions to update any row in the table without locking the table in the connection, use the nolock option.

*/
SET transaction isolation level serializable
If @ XH = 1
Begin
Select @ ID = ID from DBO. No. Material Sales
Set @ ID = @ ID + 1
Update DBO. No. Material Sales with (tablockx) Set ID = @ ID
End
Else if @ XH = 2
Begin
Select @ ID = ID from DBO. Number setting material
Set @ ID = @ ID + 1
Update DBO. Number setting material with (tablockx) Set ID = @ ID
End
Else if @ XH = 3
Begin
Select @ ID = ID from DBO. No. Project Settlement
Set @ ID = @ ID + 1
Update DBO. No. Project Settlement with (tablockx) Set ID = @ ID
End
Else if @ XH = 4
Begin
Select @ ID = ID from DBO. No. Application
Set @ ID = @ ID + 1
Update DBO. Number application with (tablockx) Set ID = @ ID
End
Else if @ XH = 5
Begin
Select @ ID = ID from DBO. No. Contract
Set @ ID = @ ID + 1
Update DBO. No. Contract with (tablockx) Set ID = @ ID
End
Else if @ XH = 6
Begin
Select @ ID = ID from DBO. No. Project Budget Estimate
Set @ ID = @ ID + 1
Update DBO. No. Project Budget Estimate with (tablockx) Set ID = @ ID
End
Else if @ XH = 7
Begin
Select @ ID = ID from DBO. No. Purchase list
Set @ ID = @ ID + 1
Update DBO. No. Purchase list with (tablockx) Set ID = @ ID
End
Else if @ XH = 8
Begin
Select @ ID = ID from DBO. Number of materials to be imported
Set @ ID = @ ID + 1
Update DBO. Import the numbered materials to the database with (tablockx) Set ID = @ ID
End
Else if @ XH = 9
Begin
Select @ ID = ID from DBO. No. Retail Materials
Set @ ID = @ ID + 1
Update DBO. No. Retail material with (tablockx) Set ID = @ ID
End
Else if @ XH = 10
Begin
Select @ ID = ID from DBO. ID overflow loss
Set @ ID = @ ID + 1
Update DBO. Number overflow loss with (tablockx) Set ID = @ ID
End
Else if @ XH = 11
Begin
Select @ ID = ID from DBO. No. Sporadic repair
Set @ ID = @ ID + 1
Update DBO. No. Sporadic repair with (tablockx) Set ID = @ ID
End

Commit transaction

Go