This was hard to figure out...

... and it doesn't even include the regex .dll I had to write in C# to make the stored procedure work! Watch it crash when I actually put it live : )

CREATE PROCEDURE [dbo].[UpdateFolderSort]
(
@folder_id int,
@folder_sort int = NULL
)

AS

DECLARE @root_decimal varchar(500)
DECLARE @folder_parent int
DECLARE @old_decimal varchar(500)
DECLARE @folder_level int
DECLARE @pos int
DECLARE @level_loop int
DECLARE @regex_prefix varchar(500)

BEGIN
SET @folder_parent = (SELECT folder_parent FROM Folders WHERE folder_id = @folder_id)
END

BEGIN
IF @folder_parent IS NOT NULL
SET @root_decimal = (SELECT folder_decimal FROM Folders WHERE folder_id = @folder_parent)
END

BEGIN
SET @old_decimal = (SELECT folder_decimal FROM Folders WHERE folder_id = @folder_id)
SET @old_decimal = RIGHT(@old_decimal, 4)
END

BEGIN
SET @folder_level = (SELECT folder_level FROM Folders WHERE folder_id = @folder_id)
END

IF @folder_level = 1
BEGIN
UPDATE
Folders
SET
folder_decimal = STUFF(folder_decimal, 1, 4, SUBSTRING('0000',1,4 - LEN(CONVERT(varchar(4), @folder_sort))) + CONVERT(varchar(4), @folder_sort))
WHERE
dbo.RegexMatch(folder_decimal, @old_decimal + '\.') = 1 AND folder_lineage LIKE '%/' + CONVERT(varchar(4), @folder_id) + '/%'
END
ELSE
BEGIN
SET @pos = ((@folder_level - 1) * 4) + @folder_level

SET @level_loop = 1
SET @regex_prefix = ''
WHILE @level_loop < @folder_level
BEGIN
SET @regex_prefix = @regex_prefix + '\d{4}\.'
SET @level_loop = @level_loop + 1
IF @level_loop = @folder_level
BREAK
ELSE
CONTINUE
END

UPDATE
Folders
SET
folder_decimal = STUFF(folder_decimal, @pos, 4, SUBSTRING('0000',1,4 - LEN(CONVERT(varchar(4), @folder_sort))) + CONVERT(varchar(4), @folder_sort))
WHERE
dbo.RegexMatch(folder_decimal, @regex_prefix + @old_decimal + '\.') = 1 AND folder_lineage LIKE '%/' + CONVERT(varchar(4), @folder_id) + '/%'
END

BEGIN
IF @root_decimal IS NOT NULL
BEGIN
UPDATE
Folders
SET
folder_sort = @folder_sort,
folder_decimal = @root_decimal + '.' + SUBSTRING('0000',1,4 - LEN(CONVERT(varchar(4), @folder_sort))) + CONVERT(varchar(4), @folder_sort)
WHERE
folder_id = @folder_id
END
ELSE
BEGIN
UPDATE
Folders
SET
folder_sort = @folder_sort,
folder_decimal = SUBSTRING('0000',1,4 - LEN(CONVERT(varchar(4), @folder_sort))) + CONVERT(varchar(4), @folder_sort)
WHERE
folder_id = @folder_id
END
END