Display records in spesific order

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 2005 Forums

Transact-SQL (2005)

Display records in spesific order

Author

Topic

metser
Starting Member

27 Posts

Posted - 2008-11-07 : 08:20:49

Hello.

I am building a web app where a user can choose the records display order. To this end, I have added a "DisplayOrder" column (smallint not null). The user can choose to move the record "down" or "up". As far as I understand, for this to work as expected, I need to get the current record (the one the user wishes to "move") and the previous or next record (depending if the user is moving the record "up" or "down" the list). Then, I need to switch between the current record's DisplayOrder number and the next or previous one (i.e for moving a record "down" the list: a=>a+1, a+1=>a).

Can someone help with the correct query/stored procedure?

Thank you all in advance.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-11-07 : 08:47:30

not something i ever use really but have you looked at cursors there is the option on scroll somewhere in the syntax

http://msdn.microsoft.com/en-us/library/ms180169.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 09:03:29

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Item VARCHAR(10),
SortOrder INT
)

INSERT @Sample
SELECT 'SQLTeam', 150 UNION ALL
SELECT 'Peso', 30 UNION ALL
SELECT 'metser', 50001

-- Prepare user supplied parameters
DECLARE @Item VARCHAR(10),
@Direction VARCHAR(4)

SELECT @Item = 'SQLTeam',
@Direction = 'down'

-- Display initial state
SELECT *
FROM @Sample
ORDER BY SortOrder

-- Do the movement
DECLARE @Place INT

UPDATE s
SET s.SortOrder = s.recID,
@Place = CASE WHEN Item = @Item THEN recID ELSE @Place END
FROM (
SELECT Item,
SortOrder,
ROW_NUMBER() OVER (ORDER BY SortOrder) AS recID
FROM @Sample
) AS s

UPDATE @Sample
SET SortOrder = CASE
WHEN @Direction = 'UP' AND SortOrder = @Place - 1 THEN @Place
WHEN @Direction = 'UP' AND SortOrder = @Place THEN @Place - 1
WHEN @Direction = 'DOWN' AND SortOrder = @Place + 1 THEN @Place
WHEN @Direction = 'DOWN' AND SortOrder = @Place THEN @Place + 1
ELSE SortOrder
END
WHERE SortOrder IN (@Place - 1, @Place, @Place + 1)

-- Display result
SELECT *
FROM @Sample
ORDER BY SortOrder[/code]

E 12°55'05.63"
N 56°04'39.26"

metser
Starting Member

27 Posts

Posted - 2008-11-07 : 10:28:40

Thank you very much. I will give it a go.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 10:31:29

Go crazy.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 10:42:14

[code]CREATE PROCEDURE dbo.spChangeDisplayOrder
(
@ItemID INT,
@Direction VARCHAR(4)
)
AS

SET NOCOUNT ON

DECLARE @Place INT

BEGIN TRANSACTION

UPDATE s
SET s.DisplayOrder = s.recID,
@Place = CASE WHEN ItemID = @ItemID THEN recID ELSE @Place END
FROM (
SELECT ItemID,
DisplayOrder,
ROW_NUMBER() OVER (ORDER BY DisplayOrder) AS recID
FROM Table1
) AS s

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not refresh displayorder.', 18, 1)
RETURN -100
END

UPDATE Table1
SET DisplayOrder = CASE
WHEN @Direction = 'UP' AND DisplayOrder = @Place - 1 THEN @Place
WHEN @Direction = 'UP' AND DisplayOrder = @Place THEN @Place - 1
WHEN @Direction = 'DOWN' AND DisplayOrder = @Place + 1 THEN @Place
WHEN @Direction = 'DOWN' AND DisplayOrder = @Place THEN @Place + 1
ELSE DisplayOrder
END
WHERE DisplayOrder IN (@Place - 1, @Place, @Place + 1)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not change displayorder.', 18, 1)
RETURN -110
END

COMMIT TRANSACTION
RETURN 0[/code]

E 12°55'05.63"
N 56°04'39.26"

metser
Starting Member

27 Posts

Posted - 2008-11-07 : 11:27:47

Thank you Peso!!! It worked like a charm!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources