use northwind
set nocount on

create table oc_onlinecasefile 
	([FILEID] int
	,[CASEID] int
	,[FILENAME] varchar(50)
	,[FIELDECRSIPTION] varchar(20)
	,[SORT ORDER] int)
go

insert oc_onlinecasefile 
select 1, 17, 'TESTING FOR TESTING', 'FOR ONLINE', 1 union all
select 2, 17, 'SUPPORTING FILE 3.txt', 'dcsdcds 1', 2 union all 
select 5, 17, '01.00.00.SqlDataProvider.txt x', 'zxghjghjgjhghj', 3  union all
select 7, 17, 'test file add.txt test file add.txt 6', 'kjhk', 4  union all
select 11, 17, 'dg.txt', 'csdcsdcsd', 5  union all
select 8, 17, 'update.txt update code', 'UPDATE', 6  union all
select 10, 17, 'DIGARDI.txt', 'YES!!!!!!!!!!', 7  union all
select 12, 32, 'DNA1.txt DNA SUPPORT', 'FILE 1 DON"T UPDATE', 1  union all
select 13, 32, 'DNA2.txt DNA SUPPORT', 'FILE 1', 2  union all
select 14, 32, 'DNA3.txt DNA SUPPORT', 'FILE 3', 3 

go

create proc oc_OnlineCaseFileSortOrder
	@fileid int
	,@CaseId int
	,@direction int
as

declare @sortOrder int
	,@msg varchar(200)

--validate @fileid
--validate @direction

begin tran
	--get sortOrder of @fileid
	select @sortOrder = [sort Order]
	from	oc_onlinecasefile 
	where	fileid = @fileid
	and	caseid = @caseid

	if @sortOrder is null
	begin
		set @msg = 'invalid fileid caseid combination'
		goto onError
	end

	if @direction < 0
	begin
		if not exists (
			select 'tg' 
			from oc_onlinecasefile 
			where [sort Order] <= @sortOrder + @direction
			and caseid = @caseid
			)
		begin
			set @msg = 'file is already first in the list'
			goto onError
		end
	end
	else
	begin
		if not exists (
			select 'tg' 
			from oc_onlinecasefile 
			where [sort Order] >= @sortOrder + @direction
			and caseid = @caseid
			)
		begin
			set @msg = 'file is already last in the list'
			goto onError
		end
	end


	--update all affected fileid sortOrders
	update oc_onlinecasefile set
		[sort Order] = [sort order] + sign(@direction) * -1
	from	oc_onlinecasefile
	where	caseid = @caseid
	and	[sort Order] >= case when @direction < 0 then @sortOrder + @direction else @sortOrder end
	and	[sort Order] <= case when @direction < 0 then @sortOrder else @sortOrder + @direction end

	--set new sortOrder of @fileid
	update oc_onlinecasefile set 
		[sort Order] = @sortOrder + @direction
	from	oc_onlinecasefile
	where	fileid = @fileid
	and	caseid = @caseid
commit tran

return 0

onError:
if @@trancount > 0 rollback tran
set @msg = isNull(@msg, 'an error occured')
raiserror (@msg, 18,1)
return -1

go


print 'original order'
select * from oc_onlinecasefile order by caseid, [sort Order]

print 'oc_OnlineCaseFileSortOrder 5, 17, -1'
exec oc_OnlineCaseFileSortOrder 5, 17, -1
select * from oc_onlinecasefile order by caseid, [sort Order]

print 'oc_OnlineCaseFileSortOrder 5, 17, 1'
exec oc_OnlineCaseFileSortOrder 5, 17, 1
select * from oc_onlinecasefile order by caseid, [sort Order]

go
drop proc oc_OnlineCaseFileSortOrder
drop table oc_onlinecasefile