记录上下移动Community_CommunitiesMoveDown

 

CREATE PROCEDURE Community_CommunitiesMoveDown
(
  @communityID int
)
AS
DECLARE @sortOrderCurrent int
DECLARE @sortOrderAbove int

-- Get Current Sort Order
SELECT
 @sortOrderCurrent = community_sortOrder
FROM Community_Communities
WHERE community_ID = @communityID

-- Get Sort Order for Section Above
SELECT @SortOrderAbove = MIN( community_sortOrder )
FROM Community_Communities
WHERE community_sortOrder > @sortOrderCurrent

-- If no row above, exit
IF @SortOrderAbove IS NULL
 return

-- Otherwise, switch sort orders
UPDATE Community_Communities SET
  community_sortOrder = @sortOrderCurrent
  WHERE community_sortOrder = @sortOrderAbove

UPDATE Community_Communities SET
  community_sortOrder = @sortOrderAbove
  WHERE community_ID = @communityID

 

GO


======================================================================

CREATE PROCEDURE Community_CommunitiesMoveUp
(
  @communityID int
)
AS
DECLARE @sortOrderCurrent int
DECLARE @sortOrderBelow int

-- Get Current Sort Order
SELECT
 @sortOrderCurrent = community_sortOrder
FROM Community_Communities
WHERE community_ID = @communityID

-- Get Sort Order for Section Below
SELECT @SortOrderBelow = MAX( community_sortOrder )
FROM Community_Communities
WHERE community_sortOrder < @sortOrderCurrent

-- If no row above, exit
IF @SortOrderBelow IS NULL
 return

-- Otherwise, switch sort orders
UPDATE Community_Communities SET
  community_sortOrder = @sortOrderCurrent
  WHERE community_sortOrder = @sortOrderBelow
 

UPDATE Community_Communities SET
  community_sortOrder = @sortOrderBelow
  WHERE community_ID = @communityID

 

GO