Objective: To replace the automatic increment function of table ID column in MSSQL by this function
Topic one: How to get a sequence value by sequence name
Method:
1. Create a table sequence, save the sequence value
2. Create a stored procedure getnextsequence to get the next sequence through it
Topic Two: How to get a sequence value from a table name
1. Create a table tablesequence, save the column in the table corresponds to which sequence
2. Create a stored procedure createtableseq, put the contents of the table tablesequence into the global temporary table @ @TmpTableSequence to improve performance
3. Create a stored procedure getnextsequencebytablename to get the next sequence through it
Topic one: How to get a sequence value by sequence name
Method:
1. Create a table sequence, save the sequence value
Table sequence:
Table name
Data type
is empty
Index
Description
Seqname
VARCHAR2 (50)
N
Pk
Sequence Name
Startwith
Number
N
Start value
INCREMENT
Number
N
Incremental
Lastvalue
Number
N
The last value of the sequence
MinValue
Number
N
Minimum value
MAXVALUE
Number
N
Maximum Value, 0 indicates none
CYCLE
Bit
N
Whether to cycle
2. Create a stored procedure getnextsequence to get the next sequence through it
Parameters:
Seqname sequence Name
Return:
The next sequence value, such as an error, returns 0
Process:
1. The stored procedure is obtained from the table sequence by parameter Seqname lastvalue
2. Lastvalue plus increment or decrement to get the next sequence value (short @newseq)
3. Update the record with @newseq
4. Return to @newseq
Topic Two: How to get a sequence value from a table name
1. Create a table tablesequence, save the column in the table corresponds to which sequence
Table Tablesequence
Table name
Data type
is empty
Index
Description
TableName
VARCHAR2 (50)
N
Pk
Table name
Seqname
VARCHAR2 (50)
N
Sequence Name
2. Create a stored procedure createtableseq, put the contents of the table tablesequence into the global temporary table @ @TmpTableSequence to improve performance
Parameters:
No
Return:
No
Process:
1. Judge whether the temporary table @ @TmpTableSequence exists
2. If it does not exist, create the temporary table (table format with tablesequence) and get the data from Tablesequence, put in the temporary table @ @TmpTableSequence
3. Create a stored procedure getnextsequencebytablename to get the next sequence through it
Parameters:
@TableName Table Name
Return:
The next sequence value, such as an error, returns 0
Process:
1. The stored procedure obtains sequence name (@seqname) from the table @ @TmpTableSequence via parameter @tablename
2. Obtain lastvalue from table sequence through @seqname
3. Lastvalue plus increment or decrement to get the next sequence value (short @newseq)
4. Update the corresponding record of the table sequnce with @newseq
Back to @newseq
The above is the theory, later, I will give the concrete practice.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
