cs_updateforumgroupsortorder.prc

解压即可使用

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_UpdateForumGroupSortOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_UpdateForumGroupSortOrder]
GO


CREATE procedure [dbo].cs_UpdateForumGroupSortOrder
(
     @GroupID int,
     @MoveUp bit,
     @SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
SET NOCOUNT ON
DECLARE @currentSortValue int
DECLARE @replaceSortValue int
DECLARE @replaceGroupID int
DECLARE @ApplicationType tinyint

	-- Get the current sort order
	SELECT @currentSortValue = SortOrder, @ApplicationType = ApplicationType FROM cs_Groups WHERE GroupID = @GroupID and SettingsID = @SettingsID
	
	-- Move the item up or down?
	IF (@MoveUp = 1)
	BEGIN
		SELECT @replaceSortValue = coalesce(fg.SortOrder, -1), @replaceGroupID = coalesce(fg.GroupID, -1)
			FROM cs_Groups fg
				inner join (
					select top 1 * 
					from cs_Groups 
					WHERE SortOrder < @currentSortValue and ApplicationType = @ApplicationType and SettingsID = @SettingsID order by SortOrder DESC
				) as pfg on 
					pfg.GroupID = fg.GroupID and fg.SettingsID = @SettingsID

		if( @replaceSortValue != -1 )
		begin	
			UPDATE cs_Groups SET SortOrder = @currentSortValue WHERE GroupID = @replaceGroupID and SettingsID = @SettingsID
    	    UPDATE cs_Groups SET SortOrder = @replaceSortValue WHERE GroupID = @GroupID and SettingsID = @SettingsID
		end
	END
	ELSE
	BEGIN
		SELECT @replaceSortValue = coalesce(fg.SortOrder, -1), @replaceGroupID = coalesce(fg.GroupID, -1)
			FROM cs_Groups fg
				inner join (
					select top 1 * 
					FROM cs_Groups 
					WHERE SortOrder > @currentSortValue and ApplicationType = @ApplicationType and SettingsID = @SettingsID order by SortOrder ASC				
				) as pfg on 
					pfg.GroupID = fg.GroupID and fg.SettingsID = @SettingsID


		if( @replaceSortValue != -1 )
		begin		
			UPDATE cs_Groups SET SortOrder = @currentSortValue WHERE GroupID = @replaceGroupID and SettingsID = @SettingsID
			UPDATE cs_Groups SET SortOrder = @replaceSortValue WHERE GroupID = @GroupID and SettingsID = @SettingsID
		end
	END
END




























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_UpdateForumGroupSortOrder] to public
go