Inserting Record into SortOrder List

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)

Inserting Record into SortOrder List

Author

Topic

rswetnam
Starting Member

4 Posts

Posted - 2008-11-29 : 22:37:05

Hi:

I have a table that I am using to provide the display of records in a specific order - the fields being CategoryID, ListName and SortOrder. Let's say for a particular category, I have 5 records with the
SortOrder fields (integers) going from 1 to 5. I would like to be able to add a new record to the table so that it shows up 2nd. That means, I suppose that the new record has to have a SortOrder of 2 and the other record currently numbered from 2 to 5 have to be increased by one.

Can anyone give me a suggestion on how I might accomplish this?

Any help greatly appreciated.
Roger

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-29 : 23:03:29

You probably want to put these in a transaction and check for errors and so on, but this is the logic you need.

CREATE PROC InsertThing
@CategoryID int,
@ListName varchar(50),
@SortOrder int
AS

UPDATE Things
SET SortOrder = SortOrder + 1
WHERE CategoryID = @CategoryID
    AND SortOrder >= @SortOrder

INSERT Things (CategoryID, ListName, SortOrder)
VALUES (@CategoryID, @ListName, @SortOrder)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-11-30 : 04:06:50

Depending on what you are doing and your data size, you might want the sort order to have gaps so you can insert ones in between. Your logic becomes more difficult because you might not have any gaps and you can get really clever limiting your updates. You update far fewer records in most cases. Worst case is the same.

Oh - and you'll need to use row_numnber() to get it out.

rswetnam
Starting Member

4 Posts

Posted - 2008-11-30 : 08:36:59

quote:
Originally posted by snSQL

You probably want to put these in a transaction and check for errors and so on, but this is the logic you need.
CREATE PROC InsertThing
@CategoryID int,
@ListName varchar(50),
@SortOrder int
AS

UPDATE Things
SET SortOrder = SortOrder + 1
WHERE CategoryID = @CategoryID
AND SortOrder >= @SortOrder

INSERT Things (CategoryID, ListName, SortOrder)
VALUES (@CategoryID, @ListName, @SortOrder)

rswetnam
Starting Member

4 Posts

Posted - 2008-11-30 : 08:38:01

Perfect - I understand - thanks so much
Roger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources