How can I re-organize the page order in SQL Server (T-SQL)

I am in the process of trying to develop a "Article" based system, There can be one Article entry in the database, then as many article data linking to that ArticleData.

Right now I am trying to update the PageOrder correctly. I want the numbers to always be consistent as in 1 to 15 for example. I don't want page numbers to go like this. 1, 1, 3, 6, 8, 9, 10... It should just be 1, 2, 3, 4, 5, 6, 7.

I have managed to complete when adding a new record for the article, as I insert a new record and then recalculate the ordering successfully. The problem is when I want to change the offical record. For example:

Lets say I have 5 Pages.

- Page 1,

- Page 2,

- Page 3,

- Page 4,

- Page 5

Lets say I want Page 3 to become Page 5

- Page 1,

- Page 2,

- Page 4,

- Page 5,

- Page 3

Now the indexing I want to be able to reindex this so that it becomes Like what it should be.

The question is now... How can I do this within a T-SQL procedure (SQL Server 2005)?

ALTER PROCEDURE [dbo].[Admin_InsertOrUpdateArticle]
    @Id int = null
    ,@ArticleId int = null
    ,@Header varchar(50)
    ,@ParentId int = null
    ,@ArticleType int
    ,@DisplayOrder int = 0
    ,@Content text
    ,@ModifiedById int
    ,@ModifiedBy varchar(50)
    ,@ModifiedDate datetime
    ,@Subject varchar(100)
    ,@NewPage int
AS
BEGIN

    SET NOCOUNT ON;

    UPDATE 
        [Article]
        SET
            [Header] = @Header
            ,[ParentId] = @ParentId
            ,[DisplayOrder] = @DisplayOrder
            ,[Type] = @ArticleType
        WHERE
            [Article].[Id] = @ArticleId

    -- If Article does not exists then we don't have any record of even Article Data!
    -- NEW RECORD!
    IF(@@ROWCOUNT = 0)
        BEGIN

            INSERT INTO [Article]
            (
                [Header]
                ,[ParentId]
                ,[DisplayOrder]
                ,[Type]
            )
            VALUES
            (
                @Header
                ,@ParentId
                ,@DisplayOrder
                ,@ArticleType
            );

            INSERT INTO [ArticleData]
            (
                [ArticleId]
                ,[Content]
                ,[CreatedBy]
                ,[CreatedById]
                ,[ModifiedBy]
                ,[ModifiedById]
                ,[PostDate]
                ,[ModifiedDate]
                ,[Subject]
                ,[PageOrder]
            )
            VALUES
            (
                @@IDENTITY
                ,@Content
                ,@ModifiedBy
                ,@ModifiedById
                ,@ModifiedBy
                ,@ModifiedById
                ,@ModifiedDate
                ,@ModifiedDate
                ,@Subject
                ,1 -- First Page!!!
            )

        END
    ELSE
        BEGIN

            -- We do have a Article Record, therefore we update the current page and check to see if it is a new page!
            UPDATE 
                [ArticleData]
                SET
                    [Content] = @Content
                    ,[Subject] = @Subject
                    ,[ModifiedBy] = @ModifiedBy
                    ,[ModifiedById] = @ModifiedById
                    ,[ModifiedDate] = @ModifiedDate
          开发者_如何学C      WHERE
                    [ArticleData].[Id] = @Id AND
                    [ArticleData].[ArticleId] = @ArticleId

            -- Are we a new Article Data?
            IF( @@ROWCOUNT = 0 )
            BEGIN   

                -- We are lets check to see if the page we want this article for exists
                IF EXISTS ( SELECT [PageOrder] FROM [ArticleData] WHERE [ArticleId] = @ArticleId AND [PageOrder] = @NewPage )
                BEGIN

                    -- It does exists therefore we need to reorder the pages
                    UPDATE 
                        [ArticleData] 
                        SET 
                            [PageOrder] = A.PageOrder + 1
                            FROM [ArticleData] A 
                        WHERE 
                            A.PageOrder >= @NewPage AND 
                            [ArticleId] = @ArticleId

                    -- We now Insert The New data that we want at that page
                    INSERT INTO [ArticleData]
                    (
                        [ArticleId]
                        ,[Content]
                        ,[CreatedBy]
                        ,[CreatedById]
                        ,[ModifiedBy]
                        ,[ModifiedById]
                        ,[PostDate]
                        ,[ModifiedDate]
                        ,[Subject]
                        ,[PageOrder]
                    )
                    VALUES
                    (
                        @ArticleId
                        ,@Content
                        ,@ModifiedBy
                        ,@ModifiedById
                        ,@ModifiedBy
                        ,@ModifiedById
                        ,@ModifiedDate
                        ,@ModifiedDate
                        ,@Subject
                        ,@NewPage
                    )   

                END     
                ELSE
                BEGIN

                    -- Since that page we want to add is not in reach we will simply add it to the very last page                   
                    INSERT INTO [ArticleData]
                    (
                        [ArticleId]
                        ,[Content]
                        ,[CreatedBy]
                        ,[CreatedById]
                        ,[ModifiedBy]
                        ,[ModifiedById]
                        ,[PostDate]
                        ,[ModifiedDate]
                        ,[Subject]
                        ,[PageOrder]
                    )
                    VALUES
                    (
                        @ArticleId
                        ,@Content
                        ,@ModifiedBy
                        ,@ModifiedById
                        ,@ModifiedBy
                        ,@ModifiedById
                        ,@ModifiedDate
                        ,@ModifiedDate
                        ,@Subject
                        ,(SELECT COUNT(Id)+1 FROM [ArticleData] WHERE [ArticleId] = @ArticleId) -- Add new Page as Last Record
                    )   

                END     

            END
            ELSE
            BEGIN

                -- The article did update therefore lets reorder our pages based on the new page defined
                -- Does this page in the article exists?  IF it does lets update the pages
                IF EXISTS ( SELECT [PageOrder] FROM [ArticleData] WHERE [ArticleId] = @ArticleId AND [PageOrder] = @NewPage )
                BEGIN

                    -- Update all pages with new numbers 
                    UPDATE 
                        [ArticleData] 
                        SET 
                            [PageOrder] = A.PageOrder + 1
                            FROM [ArticleData] A 
                        WHERE 
                            A.PageOrder >= @NewPage AND 
                            [ArticleId] = @ArticleId

                    -- Update THIS article data with the new page number
                    UPDATE 
                    [ArticleData]
                    SET
                        [PageOrder] = @NewPage
                    WHERE
                        [ArticleData].[Id] = @Id AND
                        [ArticleData].[ArticleId] = @ArticleId

                END
                ELSE
                BEGIN

                    -- Page doesn't exists because what we want to change it to is to far out of range for example if there are only 5 pages, then the ordering will simply set this page to the last index
                    UPDATE 
                    [ArticleData]
                    SET
                        [PageOrder] = (SELECT COUNT(Id) FROM [ArticleData] WHERE [Id] = @Id AND [ArticleId] = @ArticleId)
                    WHERE
                        [ArticleData].[Id] = @Id AND
                        [ArticleData].[ArticleId] = @ArticleId

                END

            END

        END

END

---

Renumbering pages is relatively simple, but I can't tell whether you're wanting to slot this into your existing code, in which case, not sure where this would go:

DECLARE @OldPageID int
DECLARE @NewPageID int

set @OldPageID = 3
set @NewPageID = 5

UPDATE
    Page
SET
    PageID = CASE
        WHEN PageID = @OldPageID THEN @NewPageID ELSE
        WHEN @OldPageID < @NewPageID THEN PageID -1 ELSE PageID + 1 END
where
    PageID between
        CASE WHEN @OldPageID < @NewPageID THEN @OldPageID ELSE @NewPageID END
        and
        CASE WHEN @OldPageID < @NewPageID THEN @NewPageID ELSE @OldPageID END

---

Here is my final result that works

ALTER PROCEDURE [dbo].[Admin_InsertOrUpdateArticle]
    @Id int = null
    ,@ArticleId int = null
    ,@Header varchar(50)
    ,@ParentId int = null
    ,@ArticleType int
    ,@DisplayOrder int = 0
    ,@Content text
    ,@ModifiedById int
    ,@ModifiedBy varchar(50)
    ,@ModifiedDate datetime
    ,@Subject varchar(100)
    ,@NewPage int
AS
BEGIN

    SET NOCOUNT ON;

    UPDATE 
        [Article]
        SET
            [Header] = @Header
            ,[ParentId] = @ParentId
            ,[DisplayOrder] = @DisplayOrder
            ,[Type] = @ArticleType
        WHERE
            [Article].[Id] = @ArticleId

    -- If Article does not exists then we don't have any record of even Article Data!
    -- NEW RECORD!
    IF(@@ROWCOUNT = 0)
        BEGIN

            INSERT INTO [Article]
            (
                [Header]
                ,[ParentId]
                ,[DisplayOrder]
                ,[Type]
            )
            VALUES
            (
                @Header
                ,@ParentId
                ,@DisplayOrder
                ,@ArticleType
            );

            INSERT INTO [ArticleData]
            (
                [ArticleId]
                ,[Content]
                ,[CreatedBy]
                ,[CreatedById]
                ,[ModifiedBy]
                ,[ModifiedById]
                ,[PostDate]
                ,[ModifiedDate]
                ,[Subject]
                ,[PageOrder]
            )
            VALUES
            (
                @@IDENTITY
                ,@Content
                ,@ModifiedBy
                ,@ModifiedById
                ,@ModifiedBy
                ,@ModifiedById
                ,@ModifiedDate
                ,@ModifiedDate
                ,@Subject
                ,1 -- First Page!!!
            )

        END
    ELSE
        BEGIN

            -- We do have a Article Record, therefore we update the current page and check to see if it is a new page!
            UPDATE 
                [ArticleData]
                SET
                    [Content] = @Content
                    ,[Subject] = @Subject
                    ,[ModifiedBy] = @ModifiedBy
                    ,[ModifiedById] = @ModifiedById
                    ,[ModifiedDate] = @ModifiedDate
                WHERE
                    [ArticleData].[Id] = @Id AND
                    [ArticleData].[ArticleId] = @ArticleId

            -- Are we a new Article Data?
            IF( @@ROWCOUNT = 0 )
            BEGIN   

                -- We are lets check to see if the page we want this article for exists
                IF EXISTS ( SELECT [PageOrder] FROM [ArticleData] WHERE [ArticleId] = @ArticleId AND [PageOrder] = @NewPage )
                BEGIN

                    -- It does exists therefore we need to reorder the pages
                    UPDATE 
                        [ArticleData] 
                        SET 
                            [PageOrder] = A.PageOrder + 1
                            FROM [ArticleData] A 
                        WHERE 
                            A.PageOrder >= @NewPage AND 
                            [ArticleId] = @ArticleId

                    -- We now Insert The New data that we want at that page
                    INSERT INTO [ArticleData]
                    (
                        [ArticleId]
                        ,[Content]
                        ,[CreatedBy]
                        ,[CreatedById]
                        ,[ModifiedBy]
                        ,[ModifiedById]
                        ,[PostDate]
                        ,[ModifiedDate]
                        ,[Subject]
                        ,[PageOrder]
                    )
                    VALUES
                    (
                        @ArticleId
                        ,@Content
                        ,@ModifiedBy
                        ,@ModifiedById
                        ,@ModifiedBy
                        ,@ModifiedById
                        ,@ModifiedDate
                        ,@ModifiedDate
                        ,@Subject
                        ,@NewPage
                    )   

                END     
                ELSE
                BEGIN

                    -- Since that page we want to add is not in reach we will simply add it to the very last page                   
                    INSERT INTO [ArticleData]
                    (
                        [ArticleId]
                        ,[Content]
                        ,[CreatedBy]
                        ,[CreatedById]
                        ,[ModifiedBy]
                        ,[ModifiedById]
                        ,[PostDate]
                        ,[ModifiedDate]
                        ,[Subject]
                        ,[PageOrder]
                    )
                    VALUES
                    (
                        @ArticleId
                        ,@Content
                        ,@ModifiedBy
                        ,@ModifiedById
                        ,@ModifiedBy
                        ,@ModifiedById
                        ,@ModifiedDate
                        ,@ModifiedDate
                        ,@Subject
                        ,(SELECT COUNT(Id)+1 FROM [ArticleData] WHERE [ArticleId] = @ArticleId) -- Add new Page as Last Record
                    )   

                END     

            END
            ELSE
            BEGIN

                -- The article did update therefore lets reorder our pages based on the new page defined        
                DECLARE @OldPage int
                SET @OldPage = (SELECT [PageOrder] FROM [ArticleData] WHERE [ArticleId] = @ArticleId AND [Id] = @Id) -- Get the old Page from the Id we have

                IF ( @OldPage != @NewPage )
                BEGIN
                    UPDATE
                        [ArticleData]
                    SET
                        PageOrder = CASE
                                WHEN PageOrder = @OldPage THEN @NewPage
                                WHEN @OldPage < @NewPage THEN PageOrder -1 ELSE PageOrder + 1 END
                    WHERE
                        PageOrder BETWEEN
                            CASE WHEN @OldPage < @NewPage THEN @OldPage ELSE @NewPage END
                            AND
                            CASE WHEN @OldPage < @NewPage THEN @NewPage ELSE @OldPage END
                END

            END

        END

END