Priorities

1. Member
Join Date
Dec 1969
Posts
70

## Priorities

A number of records are in a table (SQL2K). Each record has a unique priorityNumber, eg 1,2,3,4,5,6,7,8,9,10,.........<BR><BR>The user must be able to change priorities, one at a time. eg change 7 to 3. If 7 goes to 3, the following must happen: 7-&#062;999, 3-&#062;4, 4-&#062;5, 5-&#062;6, 6-&#062;7, 999-&#062;3.<BR><BR>It must also work from low to high, eg from 2 to 4, 1=1, 4-&#062;999, , 3-&#062;2, 4_.3, 999-&#062;4.<BR><BR>DECLARE @oldPriorityNumber TINYINT<BR>DECLARE @newPriorityNumber TINYINT<BR>DECLARE @MAXpriorityNumber INT<BR>SET @oldPriorityNumber = 7<BR>SET @newPriorityNumber = 3<BR><BR>Could anyone give me a hint on how to start here?<BR>

2. Member
Join Date
Dec 1969
Posts
70

## This is what I have got sofar......

DECLARE @oldPriorityNumber TINYINT<BR>DECLARE @newPriorityNumber TINYINT<BR>DECLARE @i INT<BR><BR>SET @oldPriorityNumber = 2<BR>SET @newPriorityNumber = 9<BR><BR>IF @oldPriorityNumber &#060; @newPriorityNumber<BR>BEGIN<BR> UPDATE tblPriority<BR> SET priorityNumber = 99<BR> WHERE priorityNumber = @newPriorityNumber<BR><BR> --here we loop through the records and update the priorities<BR> SET @i = @oldPriorityNumber + 1 <BR> DECLARE Priority_Cursor CURSOR LOCAL FAST_FORWARD FOR<BR> <BR> SELECT priorityNumber FROM tblPriority WHERE priorityNumber BETWEEN (@oldPriorityNumber +1) AND (@newPriorityNumber-1) --the number of records that need to be updated using the cursor<BR> OPEN Priority_Cursor<BR> FETCH NEXT FROM Priority_Cursor<BR> WHILE @@FETCH_STATUS = 0 --FETCH statement was successful<BR> BEGIN<BR> WHILE @i &#060; @newPriorityNumber<BR> BEGIN<BR> UPDATE tblPriority<BR> SET priorityNumber = priorityNumber + 1<BR> WHERE priorityNumber = @i<BR> SET @i = @i + 1<BR> END<BR> FETCH NEXT FROM Priority_Cursor<BR> END<BR> CLOSE Priority_Cursor<BR> DEALLOCATE Priority_Cursor<BR><BR> UPDATE tblPriority<BR> SET priorityNumber = @newPriorityNumber<BR> WHERE priorityNumber = 99<BR><BR>END<BR><BR>ELSE IF @oldPriorityNumber &#062; @newPriorityNumber<BR><BR>BEGIN<BR> UPDATE tblPriority<BR> SET priorityNumber = 99<BR> WHERE priorityNumber = @oldPriorityNumber<BR><BR> --here we loop through the records and update the priorities<BR> SET @i = @oldPriorityNumber + 1<BR> DECLARE Priority_Cursor CURSOR LOCAL FAST_FORWARD FOR<BR><BR> SELECT priorityNumber FROM tblPriority WHERE priorityNumber BETWEEN (@newPriorityNumber) AND (@oldPriorityNumber-1) --the number of records that need to be updated using the cursor<BR><BR> OPEN Priority_Cursor<BR> FETCH NEXT FROM Priority_Cursor<BR> WHILE @@FETCH_STATUS = 0 --FETCH statement was successful<BR> BEGIN<BR> WHILE @i &#060; @newPriorityNumber<BR> BEGIN<BR> UPDATE tblPriority<BR> SET priorityNumber = priorityNumber + 1<BR> WHERE priorityNumber = @i<BR> SET @i = @i + 1<BR> END<BR> FETCH NEXT FROM Priority_Cursor<BR> END<BR> CLOSE Priority_Cursor<BR> DEALLOCATE Priority_Cursor<BR><BR> UPDATE tblPriority<BR> SET priorityNumber = @newPriorityNumber<BR> WHERE priorityNumber = 99<BR><BR>END

3. Senior Member
Join Date
Dec 1969
Posts
3,195

## I think you are making this way

too complicated. ; )<BR><BR>You don&#039;t need cursors at all here. You could take all this:<BR><BR>UPDATE tblPriority <BR> SET priorityNumber = 99 <BR> WHERE priorityNumber = @newPriorityNumber <BR><BR> --here we loop through the records and update the priorities <BR> SET @i = @oldPriorityNumber + 1 <BR> DECLARE Priority_Cursor CURSOR LOCAL FAST_FORWARD FOR <BR> <BR> SELECT priorityNumber FROM tblPriority WHERE priorityNumber BETWEEN (@oldPriorityNumber +1) AND (@newPriorityNumber-1) --the number of records that need to be updated using the cursor <BR> OPEN Priority_Cursor <BR> FETCH NEXT FROM Priority_Cursor <BR> WHILE @@FETCH_STATUS = 0 --FETCH statement was successful <BR> BEGIN <BR> WHILE @i &#060; @newPriorityNumber <BR> BEGIN <BR> UPDATE tblPriority <BR> SET priorityNumber = priorityNumber + 1 <BR> WHERE priorityNumber = @i <BR> SET @i = @i + 1 <BR> END <BR> FETCH NEXT FROM Priority_Cursor <BR> END <BR> CLOSE Priority_Cursor <BR> DEALLOCATE Priority_Cursor <BR><BR> UPDATE tblPriority <BR> SET priorityNumber = @newPriorityNumber <BR> WHERE priorityNumber = 99 <BR><BR>and simplify it to just<BR><BR> UPDATE tblPriority <BR> SET priorityNumber = 99 <BR> WHERE priorityNumber = @newPriorityNumber <BR> <BR> UPDATE tblPriority <BR> SET priorityNumber = priorityNumber + 1 <BR> WHERE priorityNumber BETWEEN (@oldPriorityNumber +1) AND (@newPriorityNumber-1)<BR> <BR> UPDATE tblPriority <BR> SET priorityNumber = @newPriorityNumber <BR> WHERE priorityNumber = 99 <BR>

4. Senior Member
Join Date
Dec 1969
Posts
6,476

## Almost, But still a small flaw

You forgot the need for a switch...<BR><BR>IF @oldPriorityNumber &#060; @newPriorityNumber Then<BR> intStart = @oldPriorityNumber<BR> intEnd = @newPriorityNumber<BR> intStep = -1<BR>Else<BR> intStart = @newPriorityNumber<BR> intEnd = @oldPriorityNumber<BR> intStep = 1<BR>End IF<BR><BR>&#039;Needs it flexable to work with high numbers, <BR>&#039;all numbers are positive so move to a negative number<BR><BR>UPDATE tblPriority <BR>SET priorityNumber = -10 &#039;will never duplicate positive numbers <BR>WHERE priorityNumber = @oldPriorityNumber<BR><BR>UPDATE tblPriority <BR>SET priorityNumber = priorityNumber + intStep <BR>WHERE priorityNumber BETWEEN intStart AND intEnd<BR><BR>UPDATE tblPriority <BR>SET priorityNumber = @newPriorityNumber<BR>WHERE priorityNumber = -10<BR>

#### Posting Permissions

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