In the SQL Server implementation datasheet, move any of the record information to any location in the table

server|sqlserver| data
--Locating data
--Design ideas:
--Saves the primary key value and the new primary key value (if there is a primary key) for the record to be moved, and then compares the size of the two key values (record information in ascending order).
--If the primary key value is large, indicating that the record is moved to the new position in front, the record information of the original location can be saved and the record information from the new location will be recorded to
--The original record of the previous record of the information moved down in turn, the saved original records moved to a new location.
--If the primary key value is small, indicating that the record is moved to a new location later, you can save the record information from the original location and record information from the new location to
--The information in the next record of the original record moves up in turn, moving the saved original record to a new location. is actually the movement of the record block.
--however, if the data in the datasheet is very large, the execution efficiency of the stored procedure will decrease.


Use Zzydb

--Create an example table (Student information table)
Create Table T_studentsinfo
(i_id int identity (1,1),--the system self-increasing serial number
c_stu_id nvarchar (10),--School number
C_classname nvarchar (50),--Class
D_birthday datetime)--Date of birth
Go

--Insert 4 student information into the example table to verify the following stored procedure (Sp_myadjustrecordorder)
Insert into T_studentsinfo values (' 001 ', ' big class two or three ', ' 1978-01-25 ')
Insert into T_studentsinfo values (' 002 ', ' Big class 16 ', ' 1979-02-05 ')
Insert into T_studentsinfo values (' 003 ', ' Big Class 43 ', ' 1981-07-15 ')
Insert into T_studentsinfo values (' 004 ', ' big Class 31 ', ' 1976-01-05 ')

SELECT * FROM T_studentsinfo

If object_id (' Sp_myadjustrecordorder ') <> 0
drop proc Sp_myadjustrecordorder
Go

Create Proc sp_myadjustrecordorder (@OldStuId nvarchar), @NewStuId nvarchar (10))
As
--@OldStuid student number (used to indicate the record being moved),
--@NewStuid student number (used to indicate the new location to insert the record to be moved)
Begin
declare @Old_i_id int, @New_i_id int
Declare @i_BlockCount int--the number of record blocks that will be moved
DECLARE @i int--cyclic variable

--Get ID value
Select @Old_i_id = (select i_id from t_studentsinfo where c_stu_id = @OldStuId)
Select @New_i_id = (select i_id from t_studentsinfo where c_stu_id = @NewStuId)
Select @i_BlockCount = ABS (@Old_i_id-@New_i_id)

--Save the moved student information
Select c_stu_id, C_classname, D_birthday
into new_studentsinfo--a table that was temporarily created and deleted after it was finished
From T_studentsinfo
where c_stu_id = @OldStuId


If @New_i_id < @Old_i_id--Moves the original record information to the front
Begin
Select @i = 0
While @i <= @i_BlockCount-1
Begin
Update T_studentsinfo
Set c_stu_id = t2.c_stu_id,
C_classname = T2.c_classname,
D_birthday = T2.d_birthday
From T_studentsinfo, T_studentsinfo T2
WHERE (t_studentsinfo.i_id = @Old_i_id-@i) and
(t2.i_id = @Old_i_id-@i-1)

Select @i = @i + 1
End
End

If @New_i_id > @Old_i_id--Moves the original record information to the back
Begin
Select @i = 0
While @i <= @i_BlockCount-1
Begin
Update T_studentsinfo
Set c_stu_id = t2.c_stu_id,
C_classname = T2.c_classname,
D_birthday = T2.d_birthday
From T_studentsinfo, T_studentsinfo T2
WHERE (t_studentsinfo.i_id = @Old_i_id + @i) and
(t2.i_id = @Old_i_id + @i + 1)

Select @i = @i + 1
End
End

Update T_studentsinfo
Set c_stu_id = t3.c_stu_id,
C_classname = T3.c_classname,
D_birthday = T3.d_birthday
From T_studentsinfo T1, New_studentsinfo T3
WHERE (t1.i_id = @New_i_id)

Drop Table New_studentsinfo
--if @New_i_id = @Old_i_id-location unchanged, no processing
End
Go


--Use examples
-Request: Move the student information c_stu_id ' 004 to the position before the student information c_stu_id ' 002 '.
--Call the stored procedure Sp_myadjustrecordorder (' 004 ', ' 002 ').
Note: The i_id here must be in order.


--Backup table data information for comparison
SELECT *
Into Studentsinfobackup
From T_studentsinfo

--whether the comparisons are consistent
SELECT * FROM Studentsinfobackup
SELECT * FROM T_studentsinfo

--Moving data logging information, calling stored procedures
EXEC sp_myadjustrecordorder ' 003 ', ' 002 '

--whether the comparison result changes
SELECT * FROM Studentsinfobackup
SELECT * FROM T_studentsinfo