has been very annoying stored procedures, did not expect to help me today, perhaps because today let me slowly like the stored procedure, not much to say, cut into the topic in the use of the database, it is inevitable to use the process of deleting the operation, if the use of int type of field, so that its self-growth, this is the simplest way, But some of the consequences are not what you want! Look at this demo:1. Create such a simple table test. 2. Set the field ID for the self-increment. 3. Add data to the table insert into test (name) VALUES (' testname ') insert INTO Test (' testname ') insert into test (name) Valu Es (' testname ') 4. You will see 5. Here we delete the line with ID 2. Only two rows of data with ID 1 and ID 3 are left. Can't 6. Add a second piece of data. Insert into Test (' testname ') we will find that this may not be the result we want. Why not have ID 2? After that, you will never have a data line with an ID of 2!
This design is convenient, but the devil is the details, this blog is to solve this problem let us re-see the data row ID 2 (in this way, let the result is not only display ID 2 such an int, if one day our families ask us they want a 5-digit ID number, Starting from 00000, OK, this is fine.)
1. The main character comes into play, and the storage process finally comes in handy .
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) [email protected]
Begin
Set @[email protected]+1
Continue
End
Else
Begin
Insert Numbertest values (right (' 0000 ' +convert (varchar (5), @i), 5), @name)--Here the two number ' 5 ' is the ID length we want to set
Break
End
End
End
2. Call this stored procedure with an SQL statementExecute Insertname Test You can press a few times, dozens of times, hundreds of times, the data we want to add in, we can delete the specified ID data row, when we add again, the previously deleted ID line will be overwritten by our newly added data, This allows the ID to be linked together. Oh, yes, I haven't said how to show the beginning of ' 0 '? This simple, set the data type of the ID to nvarchar (5), it's that simple! hehe!
Summarize:
Here we call the stored procedures, stored procedures should not be used, but sometimes it is very convenient to use, this article for just the work of the children's shoes should still be a little help, well study it, life is beautiful!
The feeling of relief, finally fix a problem, here thanks to help my children's shoes! Goodbye next time!
SQL statements call this stored procedure to generate sequential encoding
