Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Question Unanswered: Change Sort_Order up/down SP problem

    Hi,

    I'm having a problem with some stored procedures which is supposed to change the position of an item in a list to move it up or down. I've written two stored procedures - one for up or down - but they work intermittenly or not at all and I'm not sure why. Could someone help me out? I'm sure my logic is messed up somewhere and probably more complicated than it needs to be.

    Thanks!


    My table structure:

    SpecialNeedID (PK, int)
    SpecialNeed (varchar)
    Sort_Order (int)

    My stored procedures:

    CREATE PROCEDURE [dbo].[up_SortSpecialNeedsUp]
    (
    @SpecialNeedID int
    )
    AS

    BEGIN
    UPDATE lkpSpecialNeeds SET Sort_Order=(
    SELECT TOP 1 Sort_Order-10 FROM lkpSpecialNeeds WHERE Sort_Order >
    (SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@SpecialNeedID))
    WHERE SpecialNeedID=
    (SELECT TOP 1 SpecialNeedID FROM lkpSpecialNeeds WHERE Sort_Order >
    (SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@SpecialNeedID) ORDER BY Sort_Order)
    END

    BEGIN
    UPDATE lkpSpecialNeeds SET Sort_Order=(
    SELECT Sort_Order+10 FROM lkpSpecialNeeds WHERE SpecialNeedID=@SpecialNeedID)
    WHERE SpecialNeedID=@SpecialNeedID
    END


    ==================================================

    CREATE PROCEDURE [dbo].[up_SortSpecialNeedsDown]
    (
    @SpecialNeedID int
    )
    AS

    BEGIN
    UPDATE lkpSpecialNeeds SET Sort_Order=(
    SELECT TOP 1 Sort_Order+10 FROM lkpSpecialNeeds WHERE Sort_Order <
    (SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@SpecialNeedID))
    WHERE SpecialNeedID=
    (SELECT TOP 1 SpecialNeedID FROM lkpSpecialNeeds WHERE Sort_Order <
    (SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@SpecialNeedID) ORDER BY Sort_Order)
    END

    BEGIN
    UPDATE lkpSpecialNeeds SET Sort_Order=(
    SELECT Sort_Order-10 FROM lkpSpecialNeeds WHERE SpecialNeedID=@SpecialNeedID)
    WHERE SpecialNeedID=@SpecialNeedID
    END

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, that is messy. For one thing, you've got some TOP statements in there without ORDER BYs, making them meaningless or even misleading.

    Explain what you are trying to do. It looks like you are trying to swap the places of two entries 10 records apart, which doesn't make sense to me. I'd think you would want to be swapping the places of neighboring records.

    Maybe some sample data would help clarify. Regardless, I think the final solution which be much simpler than what you have come up with so far.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Thanks for the reply. Here's some sample data:
    Code:
    SpecialNeedID           SpecialNeed     Sort_Order
    -----------------------------------------------
    1                                Blind                10
    2                                Deaf                20
    3                                Autistic            30

    These items are arranged in a list - the last column on the list are two buttons: up and down. Using these, the admins should be able to move items up and down to dictate the order they appear in drop-down lists.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, why are you incrementing the sort order by 10? That certainly leaves open a lot of opportunities for bad data leading to unpredictable results from your resorting procedure. Why not just increment by 1 with integers?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    try this... assuming Sort_Order is numeric... need a check for the top one

    Code:
    CREATE PROCEDURE [dbo].[up_SortSpecialNeedsUp]
    ( @CurID int )
    AS
    
    declare @PrevId int
    
    set @PrevId = (select top 1 SpecialNeedID from lkpSpecialNeeds where Sort_Order < (select Sort_Order from lkpSpecialNeeds where SpecialNeedID=@CurId) order by Sort_Order desc)
    update lkpSpecialNeeds set Sort_Order=Sort_Order-10 where SpecialNeedID = @CurId
    update lkpSpecialNeeds set Sort_Order=Sort_Order+10 where SpecialNeedID = @PrevId

  6. #6
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Quote Originally Posted by upalsen
    try this... assuming Sort_Order is numeric... need a check for the top one
    This worked perfectly, thanks so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •