Stored Procedures to sort tables

The previous company had never thought that it would soon come to this problem again. It's a different place. Table name and field name

Now the data table needs to use the sorting function, just like the previous sorting function simply copy the code of the predecessors wrote that the future will continue to use to keep it

The period is also used to a level two sorting function

Use [Istudymmmmmmm]go SET ansi_nulls ongoset quoted_identifier ongoalter PROCEDURE [dbo]. [Hobbytypeorder] Asbegin--handles the first-level type internal ordering UPDATE dbo. S_hobbytype SET Innerorderid = X.innerorderidfrom s_hobbytype JOIN (SELECT T.typeid,t.typename,t.level,t.parentid, ROW_ Number () over (ORDER by T.innerorderid) Innerorderid from dbo.  S_hobbytype T WHERE t.level = 1) X on x.typeid = S_hobbytype.typeid; --handles two-level type internal ordering UPDATE dbo. S_hobbytype SET Innerorderid = X.innerorderidfrom s_hobbytype JOIN (SELECT T.typeid,t.typename,t.level,t.parentid, ROW_ Number () over (PARTITION by T.parentid ORDER by T.innerorderid) Innerorderid from dbo.    S_hobbytype T WHERE t.level = 2) X on x.typeid = S_hobbytype.typeid; --processing global sort number with CET as (SELECT l.typeid,l.parentid,l.level, dbo. Calctreenodeorder (9,1024,level,innerorderid, NULL) Globalorder from dbo. S_hobbytype L WHERE level = 1UNION allselect l.typeid,l.parentid,l.level, dbo. Calctreenodeorder (9,1024,l.level,l.innerorderid,cet. Globalorder) Globalorder from dbo. S_Hobbytype L JOIN CET on l.parentid = CET. TypeID) UPDATE dbo. S_hobbytype SET Globalorderid = X.globalorderidfrom s_hobbytype JOIN (SELECT typeid,row_number () over (ORDER by CET. Globalorder) Globalorderid from CET) X on x.typeid = dbo.  S_hobbytype.typeid; END

 calctreenodeorder:

SET quoted_identifier ONSET ansi_nulls ongoalter FUNCTION [dbo]. [Calctreenodeorder] (@MaxLevel SMALLINT, @MaxCount BIGINT, @CurLeval SMALLINT, @CurOrder BIGINT, @PrtOrder VARBINARY (8000)) RETURNS VARBINARY (8000) asbegin DECLARE @LevelByteLength INT = LOG (@MaxCount)/log (1 DECLARE @RESULT VARBINARY (800 0) = NULL--Set parent node information IF @CurLeval > 1BEGIN Set @RESULT = SUBSTRING (@PrtOrder, 1, (@CurLeval-1) * @LevelByteLength) + C ASE @LevelByteLength when 1 then convert (VARBINARY (1), @CurOrder) while 2 then convert (VARBINARY (2), @CurOrder) when 3 then Convert (VARBINARY (3), @CurOrder) when 4 then convert (VARBINARY (4), @CurOrder) when 5 then convert (VARBINARY (5), @CurOrder When 6 then convert (VARBINARY (6), @CurOrder) while 7 then convert (VARBINARY (7), @CurOrder) when 8 then convert (VARBINARY (8) , @CurOrder) end end ELSE SET @RESULT = case @LevelByteLength if 1 then CONVERT (VARBINARY (1), @CurOrder) while 2 then CONV ERT (VARBINARY (2), @CurOrder) when 3 then CONVERT (VARBINARY (3), @CurOrder) when 4 then ConverT (VARBINARY (4), @CurOrder) when 5 then convert (VARBINARY (5), @CurOrder) while 6 then convert (VARBINARY (6), @CurOrder) when 7  Then-Convert (VARBINARY (7), @CurOrder) when 8-then-CONVERT (VARBINARY (8), @CurOrder) END while @CurLeval < @MaxLevel BEGIN SELECT @RESULT = @RESULT + Case @LevelByteLength If 1 then 0x00 when 2 then 0x0000 while 3 then 0x000000 when 4 T HEN 0x00000000 When 5 then 0x0000000000 if 6 then 0x000000000000 while 7 then 0x00000000000000 while 8 then 0x000000000000 0000 end, @CurLeval = @CurLeval + 1 END RETURN @RESULTENDGO

Stored Procedures to sort tables