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 5Lets say I want Page 3 to become Page 5
- Page 1, - Page 2, - Page 4, - Page 5, - Page 3Now 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
精彩评论