Page numbers
-
I have no idea where to start. I have a table names Pages. A field PageID, PageNumber, etc.. (those only two matters). I delete some page and need to take the one at the end of the book(?) and bring it to the place where the page where deleted (Change the PageNumber). I need to do this in a stored proc of sql function. I don't know where to start ! X| sebastien.lachance.blogspot.com
-
I have no idea where to start. I have a table names Pages. A field PageID, PageNumber, etc.. (those only two matters). I delete some page and need to take the one at the end of the book(?) and bring it to the place where the page where deleted (Change the PageNumber). I need to do this in a stored proc of sql function. I don't know where to start ! X| sebastien.lachance.blogspot.com
Here is something to get you started. It really needs to be wrapped in a transaction with a 'SERIALIZABLE' isolation level. Brutal if you have a lot of users.
DECLARE PROCEDURE DeletePage ( DECLARE @PageID INTEGER ) AS DECLARE @MaxPageNumber INTEGER SELECT @MaxPageNumber = MAX(PageNumber) FROM Pages DECLARE @PageNumber INTEGER SELECT @PageNumber = PageNumber FROM Pages WHERE PageID = @PageID IF @PageNumber IS NULL BEGIN -- Deal with Bad PageId RAISERROR('Bad PageID',16,1) RETURN 1 END DELETE FROM Pages WHERE PageID = @PageID IF @MaxPageNumber <> @PageNumber BEGIN UPDATE Pages SET PageNumber = @PageNumber WHERE PageNumber = @MaxPageNumber END RETURN 0