SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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



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

		if( @replaceSortValue != -1 And @replaceSortValue is Not Null  )
		begin	
			UPDATE cs_LinkCategories SET SortOrder = @currentSortValue WHERE LinkCategoryID = @replaceLinkCategoryID  and SettingsID = @SettingsID
			UPDATE cs_LinkCategories SET SortOrder = @replaceSortValue WHERE LinkCategoryID = @LinkCategoryID  and SettingsID = @SettingsID
		END 
	END 
	ELSE 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkCategoryID = coalesce(f.LinkCategoryID, -1)
			FROM cs_LinkCategories f
				inner join (
					select top 1 * 
					FROM cs_LinkCategories 
					WHERE SectionID = @SectionID and SortOrder > @currentSortValue  and SettingsID = @SettingsID order by SortOrder ASC				
				) as pf on 
					pf.LinkCategoryID = f.LinkCategoryID  and f.SettingsID = @SettingsID


		if( @replaceSortValue != -1 And @replaceSortValue is Not Null  )
		begin		
			UPDATE cs_LinkCategories SET SortOrder = @currentSortValue WHERE LinkCategoryID = @replaceLinkCategoryID  and SettingsID = @SettingsID
			UPDATE cs_LinkCategories SET SortOrder = @replaceSortValue WHERE LinkCategoryID = @LinkCategoryID  and SettingsID = @SettingsID
		end
	END 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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