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
ENDWell, 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.|||Thanks for the reply. Here's some sample data:
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.|||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?|||try this... assuming Sort_Order is numeric... need a check for the top one
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|||try this... assuming Sort_Order is numeric... need a check for the top one
This worked perfectly, thanks so much!
