cs_section_updatesortorder.prc

解压即可使用

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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







create procedure [dbo].cs_Section_UpdateSortOrder 
( 
            @SectionID int, 
            @MoveUp bit ,
	    @SettingsID int
) 
AS 
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN 
	set nocount on
	DECLARE @currentSortValue int 
	DECLARE @replaceSortValue int 
	DECLARE @replaceSectionID	  int
	DECLARE @GroupID	  int
	-- Get the current sort order 
	SELECT @currentSortValue = SortOrder, @GroupID = GroupID FROM cs_Sections WHERE SectionID = @SectionID 
	
	-- Move the item up or down? 
	IF (@MoveUp = 1) 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceSectionID = coalesce(f.SectionID, -1)
			FROM cs_Sections f
				inner join (
					select top 1 * 
					from cs_Sections 
					WHERE GroupID = @GroupID and SortOrder < @currentSortValue and SettingsID  = @SettingsID order by SortOrder DESC
				) as pf on 
					pf.SectionID = f.SectionID and f.SettingsID = @SettingsID

		if( @replaceSortValue != -1 )
		begin	
			UPDATE cs_Sections SET SortOrder = @currentSortValue WHERE SectionID = @replaceSectionID and SettingsID = @SettingsID
    	    UPDATE cs_Sections SET SortOrder = @replaceSortValue WHERE SectionID = @SectionID and SettingsID = @SettingsID
		END 
	END 
	ELSE 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceSectionID = coalesce(f.SectionID, -1)
			FROM cs_Sections f
				inner join (
					select top 1 * 
					FROM cs_Sections 
					WHERE GroupID = @GroupID and SortOrder > @currentSortValue order by SortOrder ASC				
				) as pf on 
					pf.SectionID = f.SectionID


		if( @replaceSortValue != -1 )
		begin		
			UPDATE cs_Sections SET SortOrder = @currentSortValue WHERE SectionID = @replaceSectionID
			UPDATE cs_Sections SET SortOrder = @replaceSortValue WHERE SectionID = @SectionID
		end
	END 
END 







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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