SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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



CREATE procedure [dbo].cs_Link_UpdateSortOrder
( 
            @LinkID int,
	    @SettingsID int, 
            @MoveUp bit 
) 
AS 
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN 
	set nocount on
	
	DECLARE @currentSortValue int 
	DECLARE @replaceSortValue int 
	DECLARE @replaceLinkID int
	DECLARE @LinkCategoryID int
	
	-- Get the current sort order 
	SELECT @currentSortValue = SortOrder, @LinkCategoryID = LinkCategoryID FROM cs_Links WHERE LinkID = @LinkID  and SettingsID = @SettingsID

	-- Move the item up or down? 
	IF (@MoveUp = 1) 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkID = coalesce(f.LinkID, -1)
			FROM cs_Links f
				inner join (
					select top 1 * 
					from cs_Links 
					WHERE LinkCategoryID = @LinkCategoryID and SortOrder < @currentSortValue and SettingsID = @SettingsID order by SortOrder DESC
				) as pf on 
					pf.LinkID = f.LinkID and f.SettingsID = @SettingsID
		if( @replaceSortValue != -1 And @replaceSortValue is Not Null )
		begin	
			UPDATE cs_Links SET SortOrder = @currentSortValue WHERE LinkID = @replaceLinkID and SettingsID = @SettingsID
			UPDATE cs_Links SET SortOrder = @replaceSortValue WHERE LinkID = @LinkID and SettingsID = @SettingsID
		END 
	END 
	ELSE 
	BEGIN 
		SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceLinkID = coalesce(f.LinkID, -1)
			FROM cs_Links f
				inner join (
					select top 1 * 
					FROM cs_Links 
					WHERE LinkCategoryID = @LinkCategoryID and SortOrder > @currentSortValue and SettingsID = @SettingsID order by SortOrder ASC				
				) as pf on 
					pf.LinkID = f.LinkID and f.SettingsID = @SettingsID


		if( @replaceSortValue != -1 And @replaceSortValue is Not Null )
		begin		
			UPDATE cs_Links SET SortOrder = @currentSortValue WHERE LinkID = @replaceLinkID and SettingsID = @SettingsID
			UPDATE cs_Links SET SortOrder = @replaceSortValue WHERE LinkID = @LinkID and SettingsID = @SettingsID
		end
	END 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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