Stored proc not working correctly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored proc not working correctly

Author

Topic

sanjeet
Starting Member

5 Posts

Posted - 2005-12-12 : 15:07:54

I have a file list inside a datagrid that looks like this:

FileName FileDescription

box box names moveup movedown
cats cat names moveup move down

moveup and move down are links that allow the userto rearrange the fileorder.

This is how the data is displayed in the database for filelist table:

FileId FileName SortOrder
1 Box 1
2 Cat 2
3 Kite 3

When the user clicks the moveup or move down order this stored procedure is called:

oc_OnlineCaseFileSortOrder (FileId, Direction)
Ex. exec oc_OnlineCaseFileSortOrder 2, 1 for moving down
oc_OnlineCaseFileSortOrder 2, -1, for moving up

The files move up and down but sometimes the I have to click several times. The problem is when a file id is updated with a new sortorder #, a sortorder already belongs to an older file so it causes problems in displayin the files in the correct file. I do have some sql logic to handle that by replacing the fileid with the older sortorder with the new sort but it does ot work. Here is my stored proc:

CREATE PROCEDURE [dbo].[oc_OnlineCaseFileSortOrder]
/*****************************************************************************************
oc_OnlineCaseFileSortOrder
**************************************************************************
Description:
Moves the sort order of the file list
Output:
None
**************************************************************************
History:
12/12/05, ssk: Created
*****************************************************************************************/
@FileId int,
@Direction int

AS
SET NOCOUNT ON

/* Declarations */

Declare @CurrentSortOrder int,
@NewSortOrder int,
@AdjFileId int

/*Get Current Info */

SELECT
@CurrentSortOrder = o.SortOrder,
@NewSortOrder = o.SortOrder + @Direction
FROM oc_onlinecasefile o
WHERE o.FileId = @FileId

/*Reorder the file list */

IF @Direction > 0
SELECT TOP 1 @AdjFileId= o.FileId
FROM dbo.oc_onlinecasefile o WITH (nolock)
WHERE o.FileId = @FileId
AND o.SortOrder >= @NewSortOrder
ORDER BY o.SortOrder
ELSE
SELECT TOP 1 @AdjFileId= o.FileId
FROM dbo.oc_onlinecasefile o WITH (nolock)
WHERE o.FileId = @FileId
AND o.SortOrder <= @NewSortOrder
ORDER BY o.SortOrder DESC

/*Set New Sort Order */

UPDATE oc_onlinecasefile
SET SortOrder = @NewSortOrder
WHERE FileId = @FileId

UPDATE oc_onlinecasefile
SET SortOrder = @CurrentSortOrder
WHERE FileId = @AdjFileId

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-12 : 18:28:10

Even if we fix your logic problems (above) you will still have concurrency issues. Try this code out: (you can run this code block as is to check it out)


use northwind
set nocount on

create table #files (fileid int, filename varchar(10), sortOrder int)
go
insert #files
select 1, 'Box', 1 union all
select 2, 'Cat', 2 union all
select 3, 'Kite', 3 union all
select 4, 'dog', 4 union all
select 5, 'mouse', 5
go

create proc oc_OnlineCaseFileSortOrder
	@fileid int
	,@direction int
as

declare @sortOrder int

--validate @fileid
--validate @direction

begin tran
	--get sortOrder of @fileid
	select @sortOrder = sortOrder 
	from	#files 
	where	fileid = @fileid

	if @direction < 0
	begin
		if not exists (select 'tg' from #files where sortOrder <= @sortOrder + @direction)
			goto onError
	end
	else
	begin
		if not exists (select 'tg' from #files where sortOrder >= @sortOrder + @direction)
			goto onError
	end


	--update all affected fileid sortOrders
	update #files set
		sortOrder = sortorder + sign(@direction) * -1
	from	#files
	where	sortOrder >= case when @direction < 0 then @sortOrder + @direction else @sortOrder end
	and	sortOrder <= case when @direction < 0 then @sortOrder else @sortOrder + @direction end

	--set new sortOrder of @fileid
	update #files set 
		sortOrder = @sortOrder + @direction
	from	#files
	where	fileid = @fileid
	
commit tran

return 0

onError:
if @@trancount > 0 rollback tran
raiserror ('invalid @direction', 18,1)
return -1

go
print 'original order'
select * from #files order by sortOrder

print 'move dog up 2'
exec oc_OnlineCaseFileSortOrder 4, -2
select * from #files order by sortOrder

print 'move dog back to original position'
exec oc_OnlineCaseFileSortOrder 4, 2
select * from #files order by sortorder	

go
drop proc oc_OnlineCaseFileSortOrder
drop table #files

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-14 : 15:38:04

this topic has continued to:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59128

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources