How to automatically generate numbers in a database

When using a database, it is inevitable that you delete it during use. If you use an int-type field to make it grow, this is the easiest way, but some of the consequences are not what you want! Take a look at this Demo:

1. create a simple table named Test. 2. set the auto-increment of the field id. 3. insert into Test (name) values ('testname ') 4. you will see 5. here we delete the row with id 2. only two rows of data with id 1 and id 3 are left. (No) 6. add another piece of data. insert into Test (name) values ('testname') we will find that this may not be the result we want. Why is there no id 2? No data lines with the id of 2 will be available even after your death!

This design is convenient, but the devil lies in the details. This blog aims to solve this problem and let us see the data row with id 2 again (here we will improve it by the way, let the result not only show an int like id 2. If one day our households ask us to have a five-digit idnumber, starting from 00000, OK, that's OK)

1. The main character appeared, and the stored procedure finally came into use. Copy codeThe Code is as follows: Create procedure [dbo]. [insertName]
(@ Name nvarchar (50 ))
As
Begin
Declare @ I int
Set @ I = 1
While (@ I <10000)
Begin
If exists (select convert (int, id) from numbertest where convert (int, id) = @ I)
Begin
Set @ I = @ I + 1
Continue
End
Else
Begin
Insert numbertest values (right ('000000' + convert (varchar (5), @ I), 5), @ name) -- the two numbers '5' here are the id length to be set.
Break
End
End
End

2. Use SQL statements to call this stored procedure

Execute insertName Test you can press it several times, dozens, hundreds of times, and add the data we want. We can delete the specified id data row, when we add a new row, the deleted id row will be overwritten by the newly added data so that the IDS can be connected. oh, by the way, I haven't said how to display the information starting with '0? This is simple. Set the id data type to nvarchar (5! Haha!
Summary:
Here we have called the stored procedure, which is not suitable for many purposes, but sometimes it is very convenient to use. This article is helpful to the children's shoes who have just been working, study hard and have a wonderful life!
I feel relieved that I have finally solved a problem. Thank you for helping me with my children's shoes! Next time!