Resequence column

12 May 2005 6:54 PM

Ian Galloway via SQLMonster.com

Any ideas how to resequence the numbers in a column after inserting or
updating a record. The column is not the Unique ID and is an integer column
it is used to allow users to select the order in which things are displayed
for them the column is displayOrder and the select statement would Order By
that column. I am trying to do this in a stored procedue but I am not
having any luck

12 May 2005 7:11 PM

Anith Sen

Please read: www.aspfaq.com/5006

Can you provide some more information including DDLs, sample data & expected
results, so that others can better understand your requirements?

--
Anith

12 May 2005 7:18 PM

Armando Prato

Assuming it's just a sequential resequencing you want and there
are no foreign key issues:

create table test (id int, name varchar(50))

insert into test select 5, 'test 1'
insert into test select 6, 'test 2'
insert into test select 9, 'test 3'
select * from test
declare @id int
set @id = 0

update test
set @id = id = @id + 1

Show quote

"Ian Galloway via SQLMonster.com" <forum@nospam.SQLMonster.com> wrote in
message news:9aea371c6f2e4868b71c18edec3f9a87@SQLMonster.com...
> Any ideas how to resequence the numbers in a column after inserting or
> updating a record. The column is not the Unique ID and is an integer
column
> it is used to allow users to select the order in which things are
displayed
> for them the column is displayOrder and the select statement would Order
By
> that column. I am trying to do this in a stored procedue but I am not
> having any luck

12 May 2005 8:10 PM

Ian Galloway via SQLMonster.com

Here is an example of what I am trying to do

tbl_Browsers

ID     browserName       browserVersion       displayOrder
1      Intern Explorer   6.5                  1
2      Intern Explorer   6.0                  2
3      Intern Explorer   5.0                  3
4      Intern Explorer   4.0                  4
5      Firefox           1.1                  5
5      Firefox           1.2                  6

the displayOrder columns holds the users preference of which order he would
like to display these records, as he inserts new records he may want the
newer record to be diplayed third in the list so I created this procedure:

CREATE PROCEDURE sp_sptInsertTicketStatusType
(@strBrowserName VarChar(20), @strBrowserVersion VarChar(225),
@intDisplayOrder Int)
AS
update tbl_Browser set displayOrder = (displayOrder + 1)
where displayOrder >= @intDisplayOrder
insert tbl_Browsers (browserName, browserVersion, displayOrder) values
(@strName, @strDescription, @intDisplayOrder)
GO

Thhis would increment all the displayOrder Values by one where the
submitted display order is equal to or greater than the newly submitted
records displayOrder.

The problem is when editing the records to update the displayOrder things
can get out of sequence that way. I need a way to do the following

Edit a record - say ID 1 (IE 6.5) and make its displayOrder 3 - I need a
way to decrement the displayOrder of the records with displayOrder of 1
and 2 (or lesser values) by one and increment the others to fit
displayOrder 3 into the sequence so ultimately the table would look like
this - of course this would be dynamic as the display order can be any
value

ID     browserName       browserVersion       displayOrder
2      Intern Explorer   6.0                  1
3      Intern Explorer   5.0                  2
1      Intern Explorer   6.5                  3
4      Intern Explorer   4.0                  4
5      Firefox           1.1                  5
5      Firefox           1.2                  6

12 May 2005 8:43 PM

Anith Sen

Not sure how you are accomodating multi-row inserts & updates with your
existing logic. Ignoring that, based on your narrative, you could do:

CREATE TABLE Browsers(
     Browser VARCHAR( 20 ) NOT NULL,
     Version DECIMAL( 2, 1 ) NOT NULL,
     DisplayOrder INT NOT NULL
         PRIMARY KEY ( Browser, Version ) ) ;

-- for inserts
BEGIN TRAN
UPDATE Browsers SET displayOrder = displayOrder + 1
WHERE displayOrder >= @DisplayOrder;
INSERT Browsers SELECT @Browser, @version, @DisplayOrder ;
COMMIT

-- for updates
BEGIN TRAN
UPDATE Browsers
   SET displayOrder = displayOrder - 1
WHERE displayOrder <= @DisplayOrder
....
UPDATE Browsers
   SET displayOrder = @DisplayOrder
WHERE Browser = 'Intern Explorer'
   AND Version = 6.5
COMMIT TRAN

--
Anith

13 May 2005 5:54 PM

Ian Galloway via SQLMonster.com

I hope this can clarify a bit more what my desired results are, using the
table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
able to select the display order of the record. For inserting new records I
use the following.

This is the isert procedure
_____________________________________________________
-- If user selects default dispayOrder (0) then add it as the last item to
display
IF @intDisplayOrder = 0
BEGIN
Set @intDisplayOrder = (select count(displayOrder) from
tbl_sptTicketStatus)+1
    BEGIN TRAN
      INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
      (@strName, @strDescription, @intDisplayOrder)
    COMMIT TRAN
END
ELSE

-- If a diplayOrder is entered into the user form then insert it into the -
- proper place and increment all records equal to or greater than it
IF @intDisplayOrder >= 1
   BEGIN
     BEGIN TRAN
       UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
       where displayOrder >= @intDisplayOrder
       INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
       (@strName, @strDescription, @intDisplayOrder)
     COMMIT TRAN
   END
__________________________
For the EDIT procedure I need to make sure that if the user changes the
diplayOrder that the other records resequence accoringly, here is what I
have now for the procedure, but it does not get the desired affect
__________________________
IF @intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
[ID] = @intID)
    BEGIN
        BEGIN TRAN
    UPDATE tbl_sptTicketStatus SET
    displayOrder = @intDisplayOrder,
    [Name] = @strName,
    [description] = @strDescription
    WHERE [ID] = @intID
        COMMIT TRAN
    END
ELSE
IF @intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
[ID] = @intID)
    BEGIN
        BEGIN TRAN
    UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
    WHERE displayOrder <= @intDisplayOrder
    UPDATE tbl_sptTicketStatus SET
    displayOrder = @intDisplayOrder,
    [Name] = @strName,
    [description] = @strDescription
    WHERE [ID] = @intID
        COMMIT TRAN
    END
ELSE
IF @intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
[ID] = @intID)
    BEGIN
        BEGIN TRAN
    UPDATE tbl_sptTicketStatus
    SET displayOrder = (displayOrder + 1)
    WHERE displayOrder > @intDisplayOrder

    UPDATE tbl_sptTicketStatus SET
    displayOrder = @intDisplayOrder,
    [Name] = @strName,
    [description] = @strDescription
    WHERE [ID] = @intID
        COMMIT TRAN
    END
GO
_______________________

so using just the ID and displayOrder column here is what should happen

original data
ID          diplayOrder
10          1
20          2
30          3
40          4

now the user may want to make ID 40 the first option in a list which should
make the table look like this

ID          diplayOrder
40          1
10          2
20          3
30          4

Then the user may choose to make the displayOrder of ID 10 to become third
which would make the table look like this

ID          diplayOrder
40          1
20          2
10          3
30          4

so when I pull the data I will use Order By displyOrder.

This and other tables using this method may have any number or rows.

Thanks for any assistance.

--
Message posted via http://www.sqlmonster.com

13 May 2005 7:13 PM

Ian Galloway via SQLMonster.com

I hope this can clarify a bit more what my desired results are, using the
table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
able to select the display order of the record. For inserting new records I
use the following.

This is the isert procedure
_____________________________________________________
-- If user selects default dispayOrder (0) then add it as the last item to
display
IF @intDisplayOrder = 0
BEGIN
Set @intDisplayOrder = (select count(displayOrder) from
tbl_sptTicketStatus)+1
   BEGIN TRAN
     INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
     (@strName, @strDescription, @intDisplayOrder)
   COMMIT TRAN
END
ELSE

-- If a diplayOrder is entered into the user form then insert it into the -
- proper place and increment all records equal to or greater than it
IF @intDisplayOrder >= 1
BEGIN
    BEGIN TRAN
      UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
      where displayOrder >= @intDisplayOrder
      INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
      (@strName, @strDescription, @intDisplayOrder)
    COMMIT TRAN
END
__________________________
For the EDIT procedure I need to make sure that if the user changes the
diplayOrder that the other records resequence accoringly, here is what I
have now for the procedure, but it does not get the desired affect
__________________________
IF @intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
[ID] = @intID)
   BEGIN
       BEGIN TRAN
    UPDATE tbl_sptTicketStatus SET
    displayOrder = @intDisplayOrder,
    [Name] = @strName,
    [description] = @strDescription
    WHERE [ID] = @intID
       COMMIT TRAN
   END
ELSE
IF @intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
[ID] = @intID)
   BEGIN
       BEGIN TRAN
    UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
    WHERE displayOrder <= @intDisplayOrder
    UPDATE tbl_sptTicketStatus SET
    displayOrder = @intDisplayOrder,
    [Name] = @strName,
    [description] = @strDescription
    WHERE [ID] = @intID
       COMMIT TRAN
   END
ELSE
IF @intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
[ID] = @intID)
   BEGIN
       BEGIN TRAN
    UPDATE tbl_sptTicketStatus
    SET displayOrder = (displayOrder + 1)
    WHERE displayOrder > @intDisplayOrder

    UPDATE tbl_sptTicketStatus SET
    displayOrder = @intDisplayOrder,
    [Name] = @strName,
    [description] = @strDescription
    WHERE [ID] = @intID
       COMMIT TRAN
   END
GO
_______________________

so using just the ID and displayOrder column here is what should happen

original data
ID          diplayOrder
10          1
20          2
30          3
40          4

now the user may want to make ID 40 the first option in a list which should
make the table look like this

ID          diplayOrder
40          1
10          2
20          3
30          4

Then the user may choose to make the displayOrder of ID 10 to become third
which would make the table look like this

ID          diplayOrder
40          1
20          2
10          3
30          4

so when I pull the data I will use Order By displyOrder.

This and other tables using this method may have any number or rows.

Thanks for any assistance.

--
Message posted via http://www.sqlmonster.com

13 May 2005 8:15 PM

Armando Prato

Here's a revision of the procedure sans any transactions. Add them where
you like.

Try it out and let me know how it works out.

create PROCEDURE sp_sptInsertTicketStatusType
(@strBrowserName VarChar(20), @strBrowserVersion VarChar(225),
@intDisplayOrder Int)
AS

declare @olddisplayorder tinyint

select @olddisplayorder = displayorder
from tbl_browser
where browserName = @strBrowserName
and browserversion = @strBrowserVersion

delete
from tbl_browser
where browserName = @strBrowserName
and browserversion = @strBrowserVersion

if @intDisplayOrder > @olddisplayorder
begin
   update tbl_Browser
   set displayOrder = (displayOrder - 1)
   where displayorder between @olddisplayorder and @intDisplayOrder
end

if @intDisplayOrder < @olddisplayorder
begin
   update tbl_Browser
   set displayOrder = (displayOrder + 1)
   where displayorder between @intDisplayOrder and @olddisplayorder
end

if (@intDisplayOrder = 0)
   select @intDisplayOrder = max(displayorder) + 1
   from tbl_Browser

insert tbl_Browser (browserName, browserVersion, displayOrder) values
(@strBrowserName, @strBrowserVersion, @intDisplayOrder)

GO

Show quote

"Ian Galloway via SQLMonster.com" <fo***@SQLMonster.com> wrote in message
news:aaf79ff55d9d4406bc34ebdab258dc6f@SQLMonster.com...
> I hope this can clarify a bit more what my desired results are, using the
> table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
> able to select the display order of the record. For inserting new records
I
> use the following.
>
> This is the isert procedure
> _____________________________________________________
> -- If user selects default dispayOrder (0) then add it as the last item to
> display
> IF @intDisplayOrder = 0
> BEGIN
> Set @intDisplayOrder = (select count(displayOrder) from
> tbl_sptTicketStatus)+1
>    BEGIN TRAN
>      INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
> values
>      (@strName, @strDescription, @intDisplayOrder)
>    COMMIT TRAN
> END
> ELSE
>
> -- If a diplayOrder is entered into the user form then insert it into
he -
> - proper place and increment all records equal to or greater than it
> IF @intDisplayOrder >= 1
>   BEGIN
>     BEGIN TRAN
>       UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
>       where displayOrder >= @intDisplayOrder
>       INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
> values
>       (@strName, @strDescription, @intDisplayOrder)
>     COMMIT TRAN
>   END
> __________________________
> For the EDIT procedure I need to make sure that if the user changes the
> diplayOrder that the other records resequence accoringly, here is what I
> have now for the procedure, but it does not get the desired affect
> __________________________
> IF @intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
> [ID] = @intID)
>    BEGIN
>        BEGIN TRAN
>     UPDATE tbl_sptTicketStatus SET
>     displayOrder = @intDisplayOrder,
>     [Name] = @strName,
>     [description] = @strDescription
>     WHERE [ID] = @intID
>        COMMIT TRAN
>    END
> ELSE
> IF @intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
> [ID] = @intID)
>    BEGIN
>        BEGIN TRAN
>     UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
>     WHERE displayOrder <= @intDisplayOrder
>     UPDATE tbl_sptTicketStatus SET
>     displayOrder = @intDisplayOrder,
>     [Name] = @strName,
>     [description] = @strDescription
>     WHERE [ID] = @intID
>        COMMIT TRAN
>    END
> ELSE
> IF @intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
> [ID] = @intID)
>    BEGIN
>        BEGIN TRAN
>     UPDATE tbl_sptTicketStatus
>     SET displayOrder = (displayOrder + 1)
>     WHERE displayOrder > @intDisplayOrder
>
>
>     UPDATE tbl_sptTicketStatus SET
>     displayOrder = @intDisplayOrder,
>     [Name] = @strName,
>     [description] = @strDescription
>     WHERE [ID] = @intID
>        COMMIT TRAN
>    END
> GO
> _______________________
>
> so using just the ID and displayOrder column here is what should happen
>
> original data
> ID          diplayOrder
> 10          1
> 20          2
> 30          3
> 40          4
>
> now the user may want to make ID 40 the first option in a list which
should
> make the table look like this
>
> ID          diplayOrder
> 40          1
> 10          2
> 20          3
> 30          4
>
> Then the user may choose to make the displayOrder of ID 10 to become third
> which would make the table look like this
>
> ID          diplayOrder
> 40          1
> 20          2
> 10          3
> 30          4
>
> so when I pull the data I will use Order By displyOrder.
>
> This and other tables using this method may have any number or rows.
>
> Thanks for any assistance.
>
> --
> Message posted via http://www.sqlmonster.com

16 May 2005 2:29 PM

Ian Galloway via SQLMonster.com

Armando Prato, that worked great and as desired. Thank you for your
assistance.

--
Message posted via http://www.sqlmonster.com

16 May 2005 3:28 PM

Ian Galloway via SQLMonster.com

After testing I realized that deleting and recreating the record was
changing the ID field of the record which is the Identity seed, where I am
using this as a foriegn key in another table this would have broken
relationships. So I modified the proc a bit to insert the existing ID of
the record as opposed to creating a new ID. In order to acheive this the
SET IDENTITY_INSERT flag had to be set to on.

I also wanted to add error checking so that if an error occurs I do not
have a half complete transaction deleting a record and not updating it
propoerly. Here is the finished code I hope now that it is set up properly,
if any one sees errors or drawbacks feel free to let me know.

CREATE PROCEDURE sptBrowserTypeEdit
(@intID Int, @strBrowserName VarChar(20), @strBrowserVersion VarChar(225),
@intDisplayOrder Int)
AS

SET IDENTITY_INSERT tbl_sptBrowsers ON

declare @olddisplayorder tinyint

select @olddisplayorder = displayorder
from tbl_sptBrowsers
where [ID] = @intID

BEGIN TRAN
delete from tbl_sptBrowsers where [ID] = @intID
COMMIT TRAN
   IF @@ERROR!=0
     BEGIN
       ROLLBACK TRAN
       RETURN
     END

if @intDisplayOrder > @olddisplayorder
BEGIN
    BEGIN TRAN
        UPDATE tbl_sptBrowsers SET displayOrder = (displayOrder - 1) WHERE
displayOrder between @olddisplayorder and @intDisplayOrder
    COMMIT TRAN
      IF @@ERROR!=0
        BEGIN
          ROLLBACK TRAN
          RETURN
        END
    END

if @intDisplayOrder < @olddisplayorder
BEGIN
    BEGIN TRAN
      UPDATE tbl_sptBrowsers SET displayOrder = (displayOrder + 1) WHERE
displayOrder between @intDisplayOrder and @olddisplayorder
    COMMIT TRAN
      IF @@ERROR!=0
        BEGIN
          ROLLBACK TRAN
          RETURN
        END
    END

IF (@intDisplayOrder = 0)
SELECT @intDisplayOrder = MAX(displayOrder) + 1 FROM tbl_sptBrowsers

BEGIN TRAN
    INSERT tbl_Browser ([ID], browserName, browserVersion, displayOrder)
    VALUES (@intID, @strBrowserName, @strBrowserVersion, @intDisplayOrder)
COMMIT TRAN
     IF @@ERROR!=0
       BEGIN
         ROLLBACK TRAN
         RETURN
       END
GO

--
Message posted via http://www.sqlmonster.com